namespace CISLib
{
    using CISLib.EncryptClasses;
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class ManageCustomer
    {
        public static SqlDataReader GetCustomerASBContractInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerASBContract where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerASBContract GetCustomerASBContractInfoViaId(Guid customerContractId)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerASBContract customerASBContract = new CustomerASBContract();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerASBContract where CustomerContractId = @CustomerContractId";
            MyDB.AddParameters(customerContractId, "CustomerContractId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerASBContract.CustomerId = MyReader.GetGuid(0);
                customerASBContract.ContractName = ManageOther.DecodeWithBytes(MyReader.GetString(1));
                customerASBContract.CompanyName = mydes.DecryptString(MyReader.GetString(2));
                customerASBContract.OrionNo = ManageOther.DecodeWithBytes(MyReader.GetString(3));
                customerASBContract.ContractValue = mydes.DecryptString(MyReader.GetString(4));
                customerASBContract.SingnedDate = MyReader.GetDateTime(5);
                customerASBContract.EffectDate = MyReader.GetDateTime(6);
                customerASBContract.CustomerContractId = MyReader.GetGuid(7);
            }
            MyDB.DBClose();
            return customerASBContract;
        }

        public static SqlDataReader GetCustomerASBEventsInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerASBEvents where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerASBEvents GetCustomerASBEventsInfoViaId(Guid customerASBEventsId)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerASBEvents customerASBEvents = new CustomerASBEvents();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerASBEvents where CustomerASBEventsId = @CustomerASBEventsId";
            MyDB.AddParameters(customerASBEventsId, "CustomerASBEventsId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerASBEvents.CustomerId = MyReader.GetGuid(0);
                customerASBEvents.ASBEventName = mydes.DecryptString(MyReader.GetString(1));
                customerASBEvents.ASBEventTime = MyReader.GetDateTime(2);
                customerASBEvents.ASBEventVenue = mydes.DecryptString(MyReader.GetString(3));
                customerASBEvents.ASBEventTopic = mydes.DecryptString(MyReader.GetString(4));
                customerASBEvents.CustomerASBEventsId = MyReader.GetGuid(5);
                customerASBEvents.ASBEventToTime = MyReader.GetDateTime(6);
            }
            MyDB.DBClose();
            return customerASBEvents;
        }

        public static SqlDataReader GetCustomerASBReceptionInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerASBReception where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerASBReception GetCustomerASBReceptionInfoViaId(Guid customerASBReceptionId)
        {
            SqlDataReader MyReader = null;
            CustomerASBReception customerASBReception = new CustomerASBReception();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerASBReception where CustomerASBReceptionId = @CustomerASBReceptionId";
            MyDB.AddParameters(customerASBReceptionId, "CustomerASBReceptionId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerASBReception.CustomerId = MyReader.GetGuid(0);
                customerASBReception.ReceptionHotel = ManageOther.DecodeWithBytes(MyReader.GetString(1));
                customerASBReception.ReceptionRestaurant = ManageOther.DecodeWithBytes(MyReader.GetString(2));
                customerASBReception.ReceptionTour = ManageOther.DecodeWithBytes(MyReader.GetString(3));
                customerASBReception.CustomerASBReceptionId = MyReader.GetGuid(4);
                customerASBReception.ReceptionTime = MyReader.GetDateTime(5);
                customerASBReception.ReceptionToTime = MyReader.GetDateTime(6);
            }
            MyDB.DBClose();
            return customerASBReception;
        }

        public static DataSet GetCustomerBasicInfo(Guid companyId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.AddParameters(companyId, "CompanyId", SqlDbType.UniqueIdentifier);
            string strSQL = "Select TB_CustomerBasicInfo.*,TB_CompanyInfo.CompanyName from TB_CustomerBasicInfo,TB_CompanyInfo where TB_CompanyInfo.CompanyId = TB_CustomerBasicInfo.CompanyId and TB_CustomerBasicInfo.CompanyId=@CompanyId";
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static DataSet GetCustomerBasicInfo(Guid catId, Guid regionId)
        {
            string strSQL;
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            if ((catId.ToString() == "00000000-0000-0000-0000-000000000000") && (regionId.ToString() == "00000000-0000-0000-0000-000000000000"))
            {
                strSQL = "Select *,TB_CompanyInfo.CompanyName from TB_CustomerBasicInfo,TB_CompanyInfo where TB_CompanyInfo.CompanyId = TB_CustomerBasicInfo.CompanyId";
            }
            else if (catId.ToString() == "00000000-0000-0000-0000-000000000000")
            {
                MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
                strSQL = "Select *,TB_CompanyInfo.CompanyName from TB_CustomerBasicInfo,TB_CompanyInfo where  TB_CustomerBasicInfo.RegionId = @RegionId and TB_CompanyInfo.CompanyId = TB_CustomerBasicInfo.CompanyId";
            }
            else if (regionId.ToString() == "00000000-0000-0000-0000-000000000000")
            {
                MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
                strSQL = "Select *,TB_CompanyInfo.CompanyName from TB_CustomerBasicInfo,TB_CompanyInfo where TB_CustomerBasicInfo.CATId = @CATId and TB_CompanyInfo.CompanyId = TB_CustomerBasicInfo.CompanyId";
            }
            else
            {
                MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
                strSQL = "Select *,TB_CompanyInfo.CompanyName from TB_CustomerBasicInfo,TB_CompanyInfo where TB_CustomerBasicInfo.CATId = @CATId and TB_CustomerBasicInfo.RegionId = @RegionId and TB_CompanyInfo.CompanyId = TB_CustomerBasicInfo.CompanyId";
            }
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static DataTable GetCustomerBasicInfo(Guid catId, Guid regionId, Guid areaId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            string strSQL = "Select *,TB_CompanyInfo.CompanyName from TB_CustomerBasicInfo,TB_CompanyInfo where TB_CustomerBasicInfo.CATId = @CATId and TB_CustomerBasicInfo.RegionId = @RegionId and TB_CustomerBasicInfo.AreaId=@AreaId and TB_CompanyInfo.CompanyId = TB_CustomerBasicInfo.CompanyId";
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet().Tables[0];
        }

        public static DataSet GetCustomerBasicInfo(Guid areaId, string strCompanyName, string strCustomerName, string strCustomerTitle, int iCustomerLevel)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters("%" + strCompanyName + "%", "CompanyName", SqlDbType.VarChar);
            MyDB.AddParameters("%" + strCustomerName + "%", "CustomerName", SqlDbType.VarChar);
            MyDB.AddParameters("%" + strCustomerTitle + "%", "CustomerTitle", SqlDbType.VarChar);
            MyDB.AddParameters(iCustomerLevel, "CustomerLevel", SqlDbType.Int);
            StringBuilder sb = new StringBuilder("");
            sb.Append("\tSELECT TB_CustomerBasicInfo.*,TB_CompanyInfo.CompanyName                                        ");
            sb.Append("\tfrom dbo.TB_CustomerBasicInfo                                                                   ");
            sb.Append("     left outer join dbo.TB_CompanyInfo                                                          ");
            sb.Append("     on dbo.TB_CustomerBasicInfo.CompanyId = dbo.TB_CompanyInfo.CompanyId                        ");
            sb.Append("     left outer join TB_Region                                                                   ");
            sb.Append("     on TB_CustomerBasicInfo.RegionId = TB_Region.RegionId                                       ");
            sb.Append("     left outer join TB_CATGroup                                                                 ");
            sb.Append("     on TB_CustomerBasicInfo.CATId = TB_CATGroup.CATGroupId                                      ");
            int posFromExpressionEnd = sb.ToString().Length;
            sb.Append("     where                                                                                       ");
            int posWhereExpressionStart = sb.ToString().Length;
            if (areaId.ToString() != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append("and (dbo.TB_CustomerBasicInfo.AreaId = @AreaId )           ");
            }
            if (strCompanyName != string.Empty)
            {
                sb.Append("and (dbo.TB_CompanyInfo.CompanyName like @CompanyName) ");
            }
            if (strCustomerName != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerBasicInfo.CustomerName like @CustomerName)");
            }
            if (strCustomerTitle != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerBasicInfo.CustomerTitle like @CustomerTitle)");
            }
            if (iCustomerLevel != 0)
            {
                sb.Append("and (dbo.TB_CustomerBasicInfo.CustomerLevel = @CustomerLevel)");
            }
            string strSQL = sb.ToString();
            if (strSQL.Length == 0x867)
            {
                strSQL = sb.ToString().Substring(0, posFromExpressionEnd - 1) + ")";
            }
            else if (strSQL.Substring(posWhereExpressionStart, 3) == "and")
            {
                string strSQL1 = strSQL.Substring(0, posWhereExpressionStart - 1);
                string strSQL2 = strSQL.Substring(posWhereExpressionStart + 3);
                strSQL = strSQL1 + strSQL2 + " order by TB_Region.RegionName,TB_CATGroup.CATGroupName";
            }
            else
            {
                strSQL = sb.ToString();
            }
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static DataSet GetCustomerBasicInfo(Guid areaId, Guid catId, Guid regionId, string strCompanyName, string strCustomerName, string strCustomerTitle, int iCustomerLevel, string strCoupon, string strTravelTool, string strDegree, string strMajor, string strGraduateSchool, string strCompany, string strWorkPlace, DateTime startDateASBContractSign, DateTime endDateASBContractSign, DateTime startDateASBContractEffect, DateTime endDateASBContractEffect, DateTime startDateEVMeeting, DateTime endDateEVMeeting, DateTime startDateVisitingAbroad, DateTime endDateVisitingAbroad, DateTime startDateASBTraining, DateTime endDateASBTraining, DateTime startDateASBEvents, DateTime endDateASBEvents, DateTime startDateEvents, DateTime endDateEvents, DateTime startDateResponsibleStaff, DateTime endDateResponsibleStaff, DateTime startDateASBReception, DateTime endDateASBReception, DateTime startDateCompetitorMeeting, DateTime endDateCompetitorMeeting, DateTime startDateCompetitorEvents, DateTime endDateCompetitorEvents, DateTime startDatePic, DateTime endDatePic, string strHotel, string strTour, Guid PrefValueId, Guid PrefTypeId, string strImageInfo, string strNewsPaper, string strEditor)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string www = PrefValueId.ToString();
            string eeee = PrefTypeId.ToString();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(PrefValueId, "PreferenceValueId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(PrefTypeId, "PreferenceId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters("%" + strCompanyName + "%", "CompanyName", SqlDbType.VarChar);
            MyDB.AddParameters("%" + strCustomerName + "%", "CustomerName", SqlDbType.VarChar);
            MyDB.AddParameters("%" + strCustomerTitle + "%", "CustomerTitle", SqlDbType.VarChar);
            MyDB.AddParameters(iCustomerLevel, "CustomerLevel", SqlDbType.Int);
            MyDB.AddParameters("%" + ManageOther.EncodeWithBytes(strCoupon) + "%", "Coupon", SqlDbType.VarChar);
            MyDB.AddParameters("%" + strTravelTool + "%", "TransportToolUsed", SqlDbType.VarChar);
            MyDB.AddParameters("%" + ManageOther.EncodeWithBytes(strDegree) + "%", "Degree", SqlDbType.VarChar);
            MyDB.AddParameters("%" + ManageOther.EncodeWithBytes(strMajor) + "%", "Major", SqlDbType.VarChar);
            MyDB.AddParameters("%" + ManageOther.EncodeWithBytes(strGraduateSchool) + "%", "GranduateSchool", SqlDbType.VarChar);
            MyDB.AddParameters("%" + ManageOther.EncodeWithBytes(strCompany) + "%", "Company", SqlDbType.VarChar);
            MyDB.AddParameters("%" + ManageOther.EncodeWithBytes(strWorkPlace) + "%", "WorkingPlace", SqlDbType.VarChar);
            MyDB.AddParameters(startDateASBContractSign, "SingnedDateStart", SqlDbType.DateTime);
            MyDB.AddParameters(endDateASBContractSign, "SingnedDateEnd", SqlDbType.DateTime);
            MyDB.AddParameters(startDateASBContractEffect, "EffectDateStart", SqlDbType.DateTime);
            MyDB.AddParameters(endDateASBContractEffect, "EffectDateEnd", SqlDbType.DateTime);
            MyDB.AddParameters(startDateEVMeeting, "EVTimeStart", SqlDbType.DateTime);
            MyDB.AddParameters(endDateEVMeeting, "EVTimeEnd", SqlDbType.DateTime);
            MyDB.AddParameters(startDateVisitingAbroad, "ABVTimeStart", SqlDbType.DateTime);
            MyDB.AddParameters(endDateVisitingAbroad, "ABVTimeEnd", SqlDbType.DateTime);
            MyDB.AddParameters(startDateASBTraining, "CustomerTrainTimeStart", SqlDbType.DateTime);
            MyDB.AddParameters(endDateASBTraining, "CustomerTrainTimeEnd", SqlDbType.DateTime);
            MyDB.AddParameters(startDateASBEvents, "ASBEventTimeStart", SqlDbType.DateTime);
            MyDB.AddParameters(endDateASBEvents, "ASBEventTimeEnd", SqlDbType.DateTime);
            MyDB.AddParameters(startDateEvents, "CustomerEventTimeStart", SqlDbType.DateTime);
            MyDB.AddParameters(endDateEvents, "CustomerEventTimeEnd", SqlDbType.DateTime);
            MyDB.AddParameters(startDateResponsibleStaff, "ResponsibleFromTimeStart", SqlDbType.DateTime);
            MyDB.AddParameters(endDateResponsibleStaff, "ResponsibleFromTimeEnd", SqlDbType.DateTime);
            MyDB.AddParameters(startDateASBReception, "ReceptionTimeStart", SqlDbType.DateTime);
            MyDB.AddParameters(endDateASBReception, "ReceptionTimeEnd", SqlDbType.DateTime);
            MyDB.AddParameters(startDateCompetitorMeeting, "CVTimeStart", SqlDbType.DateTime);
            MyDB.AddParameters(endDateCompetitorMeeting, "CVTimeEnd", SqlDbType.DateTime);
            MyDB.AddParameters(startDateCompetitorEvents, "CompetitorEventTimeStart", SqlDbType.DateTime);
            MyDB.AddParameters(endDateCompetitorEvents, "CompetitorEventTimeEnd", SqlDbType.DateTime);
            MyDB.AddParameters(startDatePic, "ImageDateStart", SqlDbType.DateTime);
            MyDB.AddParameters(endDatePic, "ImageDateEnd", SqlDbType.DateTime);
            MyDB.AddParameters("%" + ManageOther.EncodeWithBytes(strHotel) + "%", "ReceptionHotel", SqlDbType.VarChar);
            MyDB.AddParameters("%" + ManageOther.EncodeWithBytes(strTour) + "%", "ReceptionTour", SqlDbType.VarChar);
            MyDB.AddParameters("%" + ManageOther.EncodeWithBytes(strImageInfo) + "%", "ImageInfo", SqlDbType.VarChar);
            MyDB.AddParameters(strNewsPaper, "NewsPaperType", SqlDbType.VarChar);
            MyDB.AddParameters(strEditor, "LoginName", SqlDbType.VarChar);
            StringBuilder sb = new StringBuilder("");
            sb.Append("\tSELECT TB_CustomerBasicInfo.*,TB_CompanyInfo.CompanyName                                        ");
            sb.Append(" FROM dbo.TB_CustomerBasicInfo,TB_CompanyInfo                                                     ");
            sb.Append("\tWHERE dbo.TB_CustomerBasicInfo.CompanyId = TB_CompanyInfo.CompanyId                              ");
            sb.Append("       and dbo.TB_CustomerBasicInfo.CustomerId in\t\t\t\t\t\t\t\t\t\t\t\t");
            sb.Append("\t(select DISTINCT dbo.TB_CustomerBasicInfo.CustomerId                                            ");
            sb.Append("\tfrom dbo.TB_CustomerBasicInfo                                                                   ");
            sb.Append("     left outer join dbo.TB_CompanyInfo                                                          ");
            sb.Append("     on dbo.TB_CustomerBasicInfo.CompanyId = dbo.TB_CompanyInfo.CompanyId                        ");
            sb.Append("     left outer join dbo.TB_CustomerStudyExperience                                                ");
            sb.Append("     on dbo.TB_CustomerBasicInfo.CustomerId = dbo.TB_CustomerStudyExperience.CustomerId           ");
            sb.Append("     left outer join dbo.TB_CustomerTravel                                                       ");
            sb.Append("     on dbo.TB_CustomerBasicInfo.CustomerId = dbo.TB_CustomerTravel.CustomerId                   ");
            sb.Append("     left outer join dbo.TB_CustomerWorkExperience                                               ");
            sb.Append("     on dbo.TB_CustomerBasicInfo.CustomerId = dbo.TB_CustomerWorkExperience.CustomerId           ");
            sb.Append("     left outer join dbo.TB_CustomerASBReception                                                 ");
            sb.Append("\t\ton dbo.TB_CustomerBasicInfo.CustomerId = dbo.TB_CustomerASBReception.CustomerId             ");
            sb.Append("     left outer join dbo.TB_CustomerEVMeeting                                                    ");
            sb.Append("     on dbo.TB_CustomerBasicInfo.CustomerId = dbo.TB_CustomerEVMeeting.CustomerId                ");
            sb.Append("     left outer join dbo.TB_CustomerPreference\t\t\t\t\t\t\t\t\t\t\t\t\t");
            sb.Append("\t\ton dbo.TB_CustomerBasicInfo.CustomerId =dbo.TB_CustomerPreference.CustomerId     \t\t\t");
            sb.Append("     left outer join dbo.TB_CustomerImageInfo\t\t\t\t\t\t\t\t\t\t\t\t\t");
            sb.Append("\t\ton dbo.TB_CustomerBasicInfo.CustomerId =dbo.TB_CustomerImageInfo.CustomerId     \t\t\t");
            sb.Append("     left outer join dbo.TB_CustomerNewsPaper\t\t\t\t\t\t\t\t\t\t\t\t\t");
            sb.Append("\t\ton dbo.TB_CustomerBasicInfo.CustomerId =dbo.TB_CustomerNewsPaper.CustomerId     \t\t\t");
            sb.Append("     left outer join dbo.TB_SysUser\t\t\t\t\t\t\t\t\t\t\t\t\t");
            sb.Append("\t\ton dbo.TB_CustomerBasicInfo.UserId =dbo.TB_SysUser.UserId     \t\t\t");
            sb.Append("     left outer join dbo.TB_CustomerASBContract\t\t\t\t\t\t\t\t\t\t\t\t\t");
            sb.Append("\t\ton dbo.TB_CustomerBasicInfo.CustomerId =dbo.TB_CustomerASBContract.CustomerId     \t\t\t");
            sb.Append("     left outer join dbo.TB_CustomerVisitingAbroad\t\t\t\t\t\t\t\t\t\t\t\t\t");
            sb.Append("\t\ton dbo.TB_CustomerBasicInfo.CustomerId =dbo.TB_CustomerVisitingAbroad.CustomerId     \t\t\t");
            sb.Append("     left outer join dbo.TB_CustomerTraining\t\t\t\t\t\t\t\t\t\t\t\t\t");
            sb.Append("\t\ton dbo.TB_CustomerBasicInfo.CustomerId =dbo.TB_CustomerTraining.CustomerId     \t\t\t");
            sb.Append("     left outer join dbo.TB_CustomerASBEvents\t\t\t\t\t\t\t\t\t\t\t\t\t");
            sb.Append("\t\ton dbo.TB_CustomerBasicInfo.CustomerId =dbo.TB_CustomerASBEvents.CustomerId     \t\t\t");
            sb.Append("     left outer join dbo.TB_CustomerEvents\t\t\t\t\t\t\t\t\t\t\t\t\t");
            sb.Append("\t\ton dbo.TB_CustomerBasicInfo.CustomerId =dbo.TB_CustomerEvents.CustomerId     \t\t\t");
            sb.Append("     left outer join dbo.TB_CustomerResponsibleStaff\t\t\t\t\t\t\t\t\t\t\t\t\t");
            sb.Append("\t\ton dbo.TB_CustomerBasicInfo.CustomerId =dbo.TB_CustomerResponsibleStaff.CustomerId     \t\t\t");
            sb.Append("     left outer join dbo.TB_CustomerCompetitorMeeting\t\t\t\t\t\t\t\t\t\t\t\t\t");
            sb.Append("\t\ton dbo.TB_CustomerBasicInfo.CustomerId =dbo.TB_CustomerCompetitorMeeting.CustomerId     \t\t\t");
            sb.Append("     left outer join dbo.TB_CustomerCompetitorEvents\t\t\t\t\t\t\t\t\t\t\t\t\t");
            sb.Append("\t\ton dbo.TB_CustomerBasicInfo.CustomerId =dbo.TB_CustomerCompetitorEvents.CustomerId     \t\t\t");
            int posFromExpressionEnd = sb.ToString().Length;
            sb.Append("     where                                                                                       ");
            int posWhereExpressionStart = sb.ToString().Length;
            if (areaId.ToString() != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append("and (dbo.TB_CustomerBasicInfo.AreaId = @AreaId )           ");
            }
            if (catId.ToString() != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append("and (dbo.TB_CustomerBasicInfo.CATId = @CATId )           ");
            }
            if (regionId.ToString() != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append("and (dbo.TB_.CustomerBasicInfo.RegionId = @RegionId)      ");
            }
            if (strCompanyName != string.Empty)
            {
                sb.Append("and (dbo.TB_CompanyInfo.CompanyName like @CompanyName) ");
            }
            if (strCustomerName != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerBasicInfo.CustomerName like @CustomerName)");
            }
            if (strCustomerTitle != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerBasicInfo.CustomerTitle like @CustomerTitle)");
            }
            if (iCustomerLevel != 0)
            {
                sb.Append("and (dbo.TB_CustomerBasicInfo.CustomerLevel = @CustomerLevel)");
            }
            if (strImageInfo != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerImageInfo.ImageVenue like @ImageInfo or dbo.TB_CustomerImageInfo.ImagePerson like @ImageInfo or dbo.TB_CustomerImageInfo.ImageReason like @ImageInfo)");
            }
            if (strNewsPaper != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerNewsPaper.NewsPaperType = @NewsPaperType )");
            }
            if (strEditor != string.Empty)
            {
                sb.Append("and (dbo.TB_SysUser.LoginName = @LoginName )");
            }
            if (strCoupon != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerTravel.Coupon like @Coupon)");
            }
            if (strTravelTool != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerBasicInfo.TransportToolUsed like @TransportToolUsed)");
            }
            if (strDegree != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerStudyExperience.Degree like @Degree)");
            }
            if (strMajor != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerStudyExperience.Major like @Major)");
            }
            if (strGraduateSchool != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerStudyExperience.GranduateSchool like @GranduateSchool)");
            }
            if (strCompany != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerWorkExperience.Company like @Company)");
            }
            if (strWorkPlace != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerWorkExperience.WorkingPlace like @WorkingPlace)");
            }
            if (strHotel != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerASBReception.ReceptionHotel like @ReceptionHotel)");
            }
            if (strTour != string.Empty)
            {
                sb.Append("and (dbo.TB_CustomerASBReception.ReceptionTour like @ReceptionTour)");
            }
            if (PrefValueId.ToString() != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append("and (dbo.TB_CustomerPreference.PreferenceValueId = @PreferenceValueId)");
            }
            if (PrefTypeId.ToString() != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append("and (dbo.TB_CustomerPreference.PreferenceId = @PreferenceId)");
            }
            if ((startDateEVMeeting.Year != 0x708) || (endDateEVMeeting.Year != 0x270f))
            {
                sb.Append("and (dbo.TB_CustomerEVMeeting.EVTime between @EVTimeStart and @EVTimeEnd)");
            }
            if ((startDateASBContractSign.Year != 0x708) || (endDateASBContractSign.Year != 0x270f))
            {
                sb.Append("and (dbo.TB_CustomerASBContract.SingnedDate between @SingnedDateStart and @SingnedDateEnd)");
            }
            if ((startDateASBContractEffect.Year != 0x708) || (endDateASBContractEffect.Year != 0x270f))
            {
                sb.Append("and (dbo.TB_CustomerASBContract.EffectDate between @EffectDateStart and @EffectDateEnd)");
            }
            if ((startDateVisitingAbroad.Year != 0x708) || (endDateVisitingAbroad.Year != 0x270f))
            {
                sb.Append("and (dbo.TB_CustomerVisitingAbroad.ABVTime between @ABVTimeStart and @ABVTimeEnd)");
            }
            if ((startDateASBTraining.Year != 0x708) || (endDateASBTraining.Year != 0x270f))
            {
                sb.Append("and (dbo.TB_CustomerTraining.CustomerTrainTime between @CustomerTrainTimeStart and @CustomerTrainTimeEnd)");
            }
            if ((startDateASBEvents.Year != 0x708) || (endDateASBEvents.Year != 0x270f))
            {
                sb.Append("and (dbo.TB_CustomerASBEvents.ASBEventTime between @ASBEventTimeStart and @ASBEventTimeEnd)");
            }
            if ((startDateEvents.Year != 0x708) || (endDateEvents.Year != 0x270f))
            {
                sb.Append("and (dbo.TB_CustomerEvents.CustomerEventTime between @CustomerEventTimeStart and @CustomerEventTimeEnd)");
            }
            if ((startDateResponsibleStaff.Year != 0x708) || (endDateResponsibleStaff.Year != 0x270f))
            {
                sb.Append("and (dbo.TB_CustomerResponsibleStaff.ResponsibleFromTime between @ResponsibleFromTimeStart and @ResponsibleFromTimeEnd)");
            }
            if ((startDateASBReception.Year != 0x708) || (endDateASBReception.Year != 0x270f))
            {
                sb.Append("and (dbo.TB_CustomerASBReception.ReceptionTime between @ReceptionTimeStart and @ReceptionTimeEnd)");
            }
            if ((startDateCompetitorMeeting.Year != 0x708) || (endDateCompetitorMeeting.Year != 0x270f))
            {
                sb.Append("and (dbo.TB_CustomerCompetitorMeeting.CVTime between @CVTimeStart and @CVTimeEnd)");
            }
            if ((startDateCompetitorEvents.Year != 0x708) || (endDateCompetitorEvents.Year != 0x270f))
            {
                sb.Append("and (dbo.TB_CustomerCompetitorEvents.CompetitorEventTime between @CompetitorEventTimeStart and @CompetitorEventTimeEnd)");
            }
            if ((startDatePic.Year != 0x708) || (endDatePic.Year != 0x270f))
            {
                sb.Append("and (dbo.TB_CustomerImageInfo.ImageDate between @ImageDateStart and @ImageDateEnd)");
            }
            sb.Append(" )");
            string strSQL = sb.ToString();
            if (strSQL.Length == 0x867)
            {
                strSQL = sb.ToString().Substring(0, posFromExpressionEnd - 1) + ")";
            }
            else if (strSQL.Substring(posWhereExpressionStart, 3) == "and")
            {
                string strSQL1 = strSQL.Substring(0, posWhereExpressionStart - 1);
                string strSQL2 = strSQL.Substring(posWhereExpressionStart + 3);
                strSQL = strSQL1 + strSQL2;
            }
            else
            {
                strSQL = sb.ToString();
            }
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static DataSet GetCustomerBasicInfo2(Guid userId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.AddParameters(userId, "userId", SqlDbType.UniqueIdentifier);
            string strSQL = "Select TB_CustomerBasicInfo.*,TB_CompanyInfo.CompanyName from TB_CustomerBasicInfo,TB_CompanyInfo where TB_CompanyInfo.CompanyId = TB_CustomerBasicInfo.CompanyId and TB_CustomerBasicInfo.userId=@userId";
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static DataTable GetCustomerBasicInfoDownLoad(Guid catId, Guid regionId, Guid areaId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            string strSQL = "Select TB_CompanyInfo.companyname,TB_CustomerBasicInfo.CustomerName,TB_CustomerBasicInfo.CustomerGender,TB_CustomerBasicInfo.CustomerLevel,TB_CustomerBasicInfo.CustomerTitle,CustomerDepart  from TB_CustomerBasicInfo,TB_CompanyInfo where TB_CustomerBasicInfo.CATId = @CATId and TB_CustomerBasicInfo.RegionId = @RegionId and TB_CustomerBasicInfo.AreaId=@AreaId and TB_CompanyInfo.CompanyId = TB_CustomerBasicInfo.CompanyId";
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet().Tables[0];
        }

        public static DataSet GetCustomerBasicInfoForTranEdit(Guid areaId, Guid provinceId, Guid catId)
        {
            string strSQL = "";
            StringBuilder sb = new StringBuilder();
            sb.Append("select TB_CustomerBasicInfo.CustomerName,                                                                               ");
            sb.Append("\t\tTB_CustomerBasicInfo.CustomerTitle,                                                                             ");
            sb.Append("\t\tTB_CompanyInfo.CompanyName,                                                                                    ");
            sb.Append("\t\tTB_CustomerBasicInfo.CustomerGender,                                                                            ");
            sb.Append("\t\tTB_CustomerBasicInfo.CustomerDepart,                                                                            ");
            sb.Append("\t\tTB_CustomerBasicInfo.CustomerId                                                                            ");
            sb.Append("from  TB_CustomerBasicInfo  left outer join Tb_region on TB_CustomerBasicInfo.regionId = Tb_region.regionId          ");
            sb.Append("\t\t\t\t\t\t\tleft outer join dbo.TB_CATGroup on TB_CustomerBasicInfo.CATId = TB_CATGroup.CATGroupId        ");
            sb.Append("\t\t\t\t\t\t\tleft outer join dbo.TB_CompanyInfo on TB_CustomerBasicInfo.CompanyId = TB_CompanyInfo.CompanyId        ");
            sb.Append("where TB_CustomerBasicInfo.AreaId=@AreaId                                                                        ");
            if ((provinceId.ToString() != "00000000-0000-0000-0000-000000000000") || (catId.ToString() != "00000000-0000-0000-0000-000000000000"))
            {
                if (provinceId.ToString() == "00000000-0000-0000-0000-000000000000")
                {
                    sb.Append("      and TB_CustomerBasicInfo.CATId=@CatId                  ");
                }
                else if (catId.ToString() == "00000000-0000-0000-0000-000000000000")
                {
                    sb.Append("\t\t and TB_CustomerBasicInfo.RegionId=@ProvinceId           ");
                }
                else
                {
                    sb.Append("      and TB_CustomerBasicInfo.CATId=@CatId                  ");
                    sb.Append("\t\t and TB_CustomerBasicInfo.RegionId=@ProvinceId           ");
                }
            }
            strSQL = sb.ToString();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(provinceId, "ProvinceId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(catId, "CatId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static CustomerBasicInfo GetCustomerBasicInfoViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            CustomerBasicInfo customerBasicInfo = new CustomerBasicInfo();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerBasicInfo where CustomerId = @CustomerId";
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerBasicInfo.CustomerId = MyReader.GetGuid(0);
                customerBasicInfo.Name = MyReader.GetString(1);
                customerBasicInfo.Gender = MyReader.GetString(2);
                customerBasicInfo.Title = MyReader.GetString(3);
                customerBasicInfo.Level = MyReader.GetInt32(4);
                customerBasicInfo.CompanyId = MyReader.GetGuid(5);
                customerBasicInfo.Depart = MyReader.GetString(6);
                customerBasicInfo.CatId = MyReader.GetGuid(7);
                customerBasicInfo.RegionId = MyReader.GetGuid(8);
                customerBasicInfo.AreaId = MyReader.GetGuid(9);
                customerBasicInfo.UserId = MyReader.GetGuid(10);
                customerBasicInfo.CustomerDefId = MyReader.GetGuid(12);
                customerBasicInfo.CustomerEmail = MyReader.GetString(13);
                customerBasicInfo.MailAdd = MyReader.GetString(14);
            }
            MyDB.DBClose();
            return customerBasicInfo;
        }

        public static SqlDataReader GetCustomerBasicInfoViaUserId(Guid userId)
        {
            string strSQL = "Select * from TB_CustomerBasicInfo where UserId=@UserId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetCustomerBasicInfoViaUserId(Guid userId, string catId, string regionId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "Select * from TB_CustomerBasicInfo where UserId=@UserId";
            if (catId != string.Empty)
            {
                strSQL = strSQL + " and CATId=@CATId";
                MyDB.AddParameters(new Guid(catId), "CATId", SqlDbType.UniqueIdentifier);
            }
            if (regionId != string.Empty)
            {
                strSQL = strSQL + " AND RegionId = @RegionId";
                MyDB.AddParameters(new Guid(regionId), "RegionId", SqlDbType.UniqueIdentifier);
            }
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetCustomerCompetitorEventsInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerCompetitorEvents where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerCompetitorEvents GetCustomerCompetitorEventsInfoViaId(Guid customerCompetitorEventsId)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerCompetitorEvents customerCompetitorEvents = new CustomerCompetitorEvents();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerCompetitorEvents where CustomerCompetitorEventsId = @CustomerCompetitorEventsId";
            MyDB.AddParameters(customerCompetitorEventsId, "CustomerCompetitorEventsId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerCompetitorEvents.CustomerId = MyReader.GetGuid(0);
                customerCompetitorEvents.CompetitorEventName = mydes.DecryptString(MyReader.GetString(1));
                customerCompetitorEvents.CompetitorEventTime = MyReader.GetDateTime(2);
                customerCompetitorEvents.CompetitorEventVenue = mydes.DecryptString(MyReader.GetString(3));
                customerCompetitorEvents.CompetitorEventTopic = mydes.DecryptString(MyReader.GetString(4));
                customerCompetitorEvents.CustomerCompetitorEventsId = MyReader.GetGuid(5);
                customerCompetitorEvents.CompetitorEventToTime = MyReader.GetDateTime(6);
            }
            MyDB.DBClose();
            return customerCompetitorEvents;
        }

        public static SqlDataReader GetCustomerCompetitorMeetingInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerCompetitorMeeting where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerCompetitorMeeting GetCustomerCompetitorMeetingInfoViaId(Guid customerCompetitorMeetingId)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerCompetitorMeeting customerCompetitorMeeting = new CustomerCompetitorMeeting();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerCompetitorMeeting where CustomerCompetitorMeetingId = @CustomerCompetitorMeetingId";
            MyDB.AddParameters(customerCompetitorMeetingId, "CustomerCompetitorMeetingId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerCompetitorMeeting.CustomerId = MyReader.GetGuid(0);
                customerCompetitorMeeting.CVTime = MyReader.GetDateTime(1);
                customerCompetitorMeeting.CVVenue = mydes.DecryptString(MyReader.GetString(2));
                customerCompetitorMeeting.CVExcutives = mydes.DecryptString(MyReader.GetString(3));
                customerCompetitorMeeting.CVReasons = mydes.DecryptString(MyReader.GetString(4));
                customerCompetitorMeeting.CustomerCompetitorMeetingId = MyReader.GetGuid(5);
                customerCompetitorMeeting.CCompanyName = mydes.DecryptString(MyReader.GetString(6));
            }
            MyDB.DBClose();
            return customerCompetitorMeeting;
        }

        public static CustomerCostumeInfo GetCustomerCostumeInfoViaId(Guid id)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerCostumeInfo customerCostumeInfo = new CustomerCostumeInfo();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerCostume where CustomerId = @CustomerId";
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerCostumeInfo.CustomerId = MyReader.GetGuid(0);
                customerCostumeInfo.NeckMeasurement = mydes.DecryptString(MyReader.GetString(1));
                customerCostumeInfo.ShoesSize = mydes.DecryptString(MyReader.GetString(2));
                customerCostumeInfo.HipMeasurement = mydes.DecryptString(MyReader.GetString(3));
                customerCostumeInfo.BrandPre = mydes.DecryptString(MyReader.GetString(5));
            }
            MyDB.DBClose();
            return customerCostumeInfo;
        }

        public static SqlDataReader GetCustomerEventsInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerEvents where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerEvents GetCustomerEventsInfoViaId(Guid customerEventsId)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerEvents customerEvents = new CustomerEvents();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerEvents where CustomerEventsId = @CustomerEventsId";
            MyDB.AddParameters(customerEventsId, "CustomerEventsId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerEvents.CustomerId = MyReader.GetGuid(0);
                customerEvents.CustomerEventName = mydes.DecryptString(MyReader.GetString(1));
                customerEvents.CustomerEventTime = MyReader.GetDateTime(2);
                customerEvents.CustomerEventVenue = mydes.DecryptString(MyReader.GetString(3));
                customerEvents.CustomerEventTopic = mydes.DecryptString(MyReader.GetString(4));
                customerEvents.CustomerEventGift = mydes.DecryptString(MyReader.GetString(5));
                customerEvents.CustomerEventsId = MyReader.GetGuid(6);
                customerEvents.CustomerEventToTime = MyReader.GetDateTime(7);
            }
            MyDB.DBClose();
            return customerEvents;
        }

        public static SqlDataReader GetCustomerEVMeetingInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerEVMeeting where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerEVMeeting GetCustomerEVMeetingInfoViaId(Guid customerEVMeetingId)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerEVMeeting customerEVMeeting = new CustomerEVMeeting();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerEVMeeting where CustomerEVMeetingId = @CustomerEVMeetingId";
            MyDB.AddParameters(customerEVMeetingId, "CustomerEVMeetingId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerEVMeeting.CustomerId = MyReader.GetGuid(0);
                customerEVMeeting.EVTime = MyReader.GetDateTime(1);
                customerEVMeeting.EVVenue = mydes.DecryptString(MyReader.GetString(2));
                customerEVMeeting.EVExecutives = mydes.DecryptString(MyReader.GetString(3));
                customerEVMeeting.EVReasons = mydes.DecryptString(MyReader.GetString(4));
                customerEVMeeting.CustomerEVMeetingId = MyReader.GetGuid(5);
            }
            MyDB.DBClose();
            return customerEVMeeting;
        }

        public static SqlDataReader GetCustomerFamilyInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerFamilyInfo where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerFamilyInfo GetCustomerFamilyInfoViaId(Guid customerFamilyInfoId)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerFamilyInfo customerFamilyInfo = new CustomerFamilyInfo();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerFamilyInfo where CustomerFamilyInfoId = @CustomerFamilyInfoId";
            MyDB.AddParameters(customerFamilyInfoId, "CustomerFamilyInfoId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerFamilyInfo.CustomerId = MyReader.GetGuid(0);
                customerFamilyInfo.MemberName = mydes.DecryptString(MyReader.GetString(1));
                customerFamilyInfo.Relationship = mydes.DecryptString(MyReader.GetString(2));
                customerFamilyInfo.MBirthDate = MyReader.GetDateTime(3);
                customerFamilyInfo.MSchoolCompany = mydes.DecryptString(MyReader.GetString(4));
                customerFamilyInfo.MPhone = mydes.DecryptString(MyReader.GetString(5));
                customerFamilyInfo.OtherInfo = mydes.DecryptString(MyReader.GetString(6));
                customerFamilyInfo.CustomerFamilyInfoId = MyReader.GetGuid(7);
            }
            MyDB.DBClose();
            return customerFamilyInfo;
        }

        public static CustomerHealth GetCustomerHealthViaId(Guid id)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerHealth customerHealth = new CustomerHealth();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerHealth where CustomerId = @CustomerId";
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerHealth.CustomerId = MyReader.GetGuid(0);
                customerHealth.HealthStatus = MyReader.GetInt32(1);
                customerHealth.Eyesight = MyReader.GetInt32(2);
                customerHealth.MajorOperation = mydes.DecryptString(MyReader.GetString(3));
                customerHealth.SpecialDisease = mydes.DecryptString(MyReader.GetString(4));
                customerHealth.Allergy = mydes.DecryptString(MyReader.GetString(5));
            }
            MyDB.DBClose();
            return customerHealth;
        }

        public static Guid[] GetCustomerIdAryInCustomerBasicInfo(Guid catId, Guid regionId, Guid areaId)
        {
            DBAccess MyDB = new DBAccess();
            SqlDataReader MyReader = null;
            ArrayList List = new ArrayList();
            MyDB.DBOpen();
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            string strSQL = "Select CustomerId from TB_CustomerBasicInfo where TB_CustomerBasicInfo.CATId = @CATId and TB_CustomerBasicInfo.RegionId = @RegionId and TB_CustomerBasicInfo.AreaId=@AreaId ";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                Guid id = (Guid) MyReader[0];
                List.Add(id);
            }
            MyDB.DBClose();
            return (Guid[]) List.ToArray(typeof(Guid));
        }

        public static SqlDataReader GetCustomerImageInfo(Guid customerId)
        {
            string strSQL = "Select * from Tb_CustomerImageInfo where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerImageInfo GetCustomerImageInfoViaId(Guid imageId)
        {
            SqlDataReader MyReader = null;
            CustomerImageInfo customerImageInfo = new CustomerImageInfo();
            string strSQL = "Select * from Tb_CustomerImageInfo where ImageId=@ImageId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(imageId, "ImageId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerImageInfo.CustomerId = MyReader.GetGuid(0);
                customerImageInfo.ImageId = MyReader.GetGuid(1);
                customerImageInfo.ImageDate = MyReader.GetDateTime(2);
                customerImageInfo.ImageVenue = ManageOther.DecodeWithBytes(MyReader.GetString(3));
                customerImageInfo.ImagePerson = ManageOther.DecodeWithBytes(MyReader.GetString(4));
                customerImageInfo.ImageReason = ManageOther.DecodeWithBytes(MyReader.GetString(5));
            }
            MyDB.DBClose();
            return customerImageInfo;
        }

        public static CustomerImage GetCustomerImageObjectInfoViaId(Guid imageId)
        {
            SqlDataReader MyReader = null;
            CustomerImage customerImage = new CustomerImage();
            string strSQL = "Select * from Tb_CustomerImage where ImageId=@ImageId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(imageId, "ImageId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerImage.ImageId = MyReader.GetGuid(0);
                customerImage.ImageType = MyReader.GetString(1);
                customerImage.ImageObject = (byte[]) MyReader["ImageObject"];
            }
            MyDB.DBClose();
            return customerImage;
        }

        public static CustomerNewsPaper[] GetCustomerNewsPaperAryViaId(Guid customerId)
        {
            SqlDataReader MyReader = null;
            ArrayList strList = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "Select * from TB_CustomerNewsPaper where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                try
                {
                    CustomerNewsPaper customerNewsPaper = new CustomerNewsPaper();
                    customerNewsPaper.CustomerId = MyReader.GetGuid(0);
                    customerNewsPaper.CustomerNewsPaperId = MyReader.GetGuid(2);
                    customerNewsPaper.NewsPaperType = MyReader.GetString(1);
                    strList.Add(customerNewsPaper);
                    continue;
                }
                catch (Exception)
                {
                    return null;
                }
            }
            MyDB.DBClose();
            return (CustomerNewsPaper[]) strList.ToArray(typeof(CustomerNewsPaper));
        }

        public static CustomerPersonalInfo GetCustomerPersonalInfoViaId(Guid id)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerPersonalInfo customerPersonalInfo = new CustomerPersonalInfo();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerPersonalInfo where CustomerId = @CustomerId";
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerPersonalInfo.CustomerId = MyReader.GetGuid(0);
                customerPersonalInfo.BirthDate = MyReader.GetDateTime(1);
                customerPersonalInfo.MarriageDate = MyReader.GetDateTime(2);
                customerPersonalInfo.BirthPlace = mydes.DecryptString(MyReader.GetString(3));
                customerPersonalInfo.FamilyAdd = mydes.DecryptString(MyReader.GetString(4));
                customerPersonalInfo.RelocationAdd = mydes.DecryptString(MyReader.GetString(5));
                customerPersonalInfo.PersonalTele = mydes.DecryptString(MyReader.GetString(6));
                customerPersonalInfo.Mobile = mydes.DecryptString(MyReader.GetString(8));
                customerPersonalInfo.BloodType = mydes.DecryptString(MyReader.GetString(9));
                customerPersonalInfo.Weight = mydes.DecryptString(MyReader.GetString(10));
                customerPersonalInfo.Height = mydes.DecryptString(MyReader.GetString(11));
                customerPersonalInfo.SpecicalHobbies = mydes.DecryptString(MyReader.GetString(12));
            }
            MyDB.DBClose();
            return customerPersonalInfo;
        }

        public static SqlDataReader GetCustomerRelationsInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerRelations where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerRelations GetCustomerRelationsInfoViaId(Guid customerRelationsId)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerRelations customerRelations = new CustomerRelations();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerRelations where CustomerRelationsId = @CustomerRelationsId";
            MyDB.AddParameters(customerRelationsId, "CustomerRelationsId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerRelations.CustomerId = MyReader.GetGuid(0);
                customerRelations.Relations = mydes.DecryptString(MyReader.GetString(1));
                customerRelations.CustomerRelationsId = MyReader.GetGuid(2);
            }
            MyDB.DBClose();
            return customerRelations;
        }

        public static SqlDataReader GetCustomerResponsibleStaffInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerResponsibleStaff where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerResponsibleStaff GetCustomerResponsibleStaffInfoViaId(Guid customerResponsibleStaffId)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerResponsibleStaff customerResponsibleStaff = new CustomerResponsibleStaff();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerResponsibleStaff where CustomerResponsibleStaffId = @CustomerResponsibleStaffId";
            MyDB.AddParameters(customerResponsibleStaffId, "CustomerResponsibleStaffId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerResponsibleStaff.CustomerId = MyReader.GetGuid(0);
                customerResponsibleStaff.StaffName = mydes.DecryptString(MyReader.GetString(1));
                customerResponsibleStaff.StaffTile = mydes.DecryptString(MyReader.GetString(2));
                customerResponsibleStaff.CustomerResponsibleStaffId = MyReader.GetGuid(3);
                customerResponsibleStaff.ResponsibleFromTime = MyReader.GetDateTime(5);
                customerResponsibleStaff.ResponsibleToTime = MyReader.GetDateTime(6);
            }
            MyDB.DBClose();
            return customerResponsibleStaff;
        }

        public static Guid[] GetCustomerSchoolIdAryViaCustomerTrainId(Guid customerTrainId)
        {
            SqlDataReader MyReader = null;
            ArrayList List = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerTrainId, "CustomerTrainId", SqlDbType.UniqueIdentifier);
            string strSQL = "Select CustomerSchoolId from TB_CustomerSchoolTypeMapping where CustomerTrainId=@CustomerTrainId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                Guid tmpId = new Guid();
                tmpId = MyReader.GetGuid(0);
                List.Add(tmpId);
            }
            MyDB.DBClose();
            return (Guid[]) List.ToArray(typeof(Guid));
        }

        public static SqlDataReader GetCustomerStudyExperienceInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerStudyExperience where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerStudyExperience GetCustomerStudyExperienceInfoViaId(Guid studyExpId)
        {
            SqlDataReader MyReader = null;
            CustomerStudyExperience customerStudyExperience = new CustomerStudyExperience();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerStudyExperience where CustomerStudyExpId = @CustomerStudyExpId";
            MyDB.AddParameters(studyExpId, "CustomerStudyExpId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerStudyExperience.CustomerId = MyReader.GetGuid(0);
                customerStudyExperience.Degree = ManageOther.DecodeWithBytes(MyReader.GetString(1));
                customerStudyExperience.Major = ManageOther.DecodeWithBytes(MyReader.GetString(2));
                customerStudyExperience.GranduateSchool = ManageOther.DecodeWithBytes(MyReader.GetString(3));
                customerStudyExperience.Sponsor = ManageOther.DecodeWithBytes(MyReader.GetString(4));
                customerStudyExperience.StartDate = MyReader.GetDateTime(5);
                customerStudyExperience.EndDate = MyReader.GetDateTime(6);
                customerStudyExperience.CustomerStudyExpId = MyReader.GetGuid(7);
            }
            MyDB.DBClose();
            return customerStudyExperience;
        }

        public static SqlDataReader GetCustomerTrainInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerTraining where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerTraining GetCustomerTrainInfoViaId(Guid customerTrainId)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerTraining customerTraining = new CustomerTraining();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerTraining where CustomerTrainId = @CustomerTrainId";
            MyDB.AddParameters(customerTrainId, "CustomerTrainId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerTraining.CustomerId = MyReader.GetGuid(0);
                customerTraining.CustomerTrainTypeId = MyReader.GetGuid(1);
                customerTraining.CustomerTrainTime = MyReader.GetDateTime(2);
                customerTraining.CustomerTrainToTime = MyReader.GetDateTime(3);
                customerTraining.CustomerSchoolIdAry = GetCustomerSchoolIdAryViaCustomerTrainId(MyReader.GetGuid(4));
                customerTraining.CustomerTrainId = MyReader.GetGuid(4);
            }
            MyDB.DBClose();
            return customerTraining;
        }

        public static SqlDataReader GetCustomerTravelInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerTravel where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static string GetCustomerTravelInfoOfTranTool(Guid customerId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select TransportToolUsed from TB_CustomerBasicInfo where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            object obj = MyDB.DBExecuteScalar();
            if (obj == null)
            {
                MyDB.DBClose();
                return "";
            }
            MyDB.DBClose();
            return obj.ToString();
        }

        public static CustomerTravel GetCustomerTravelInfoViaId(Guid customerTravelId)
        {
            SqlDataReader MyReader = null;
            CustomerTravel customerTravel = new CustomerTravel();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerTravel where CustomerTravelId = @CustomerTravelId";
            MyDB.AddParameters(customerTravelId, "CustomerTravelId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerTravel.CustomerId = MyReader.GetGuid(0);
                customerTravel.Coupon = ManageOther.DecodeWithBytes(MyReader.GetString(2));
                customerTravel.Other = ManageOther.DecodeWithBytes(MyReader.GetString(3));
                customerTravel.CustomerTravelId = MyReader.GetGuid(4);
            }
            MyDB.DBClose();
            return customerTravel;
        }

        public static SqlDataReader GetCustomerVisitingAbroadInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerVisitingAbroad where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerVisitingAbroad GetCustomerVisitingAbroadInfoViaId(Guid customerVisitingAbroadId)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerVisitingAbroad customerVisitingAbroad = new CustomerVisitingAbroad();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerVisitingAbroad where CustomerVisitingAbroadId = @CustomerVisitingAbroadId";
            MyDB.AddParameters(customerVisitingAbroadId, "CustomerVisitingAbroadId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerVisitingAbroad.CustomerId = MyReader.GetGuid(0);
                customerVisitingAbroad.ABTime = MyReader.GetDateTime(1);
                customerVisitingAbroad.ABVenue = mydes.DecryptString(MyReader.GetString(2));
                customerVisitingAbroad.MainActivity = mydes.DecryptString(MyReader.GetString(3));
                customerVisitingAbroad.ASBAccompany = mydes.DecryptString(MyReader.GetString(4));
                customerVisitingAbroad.CustomerVisitingAbroadId = MyReader.GetGuid(5);
                customerVisitingAbroad.ABToTime = MyReader.GetDateTime(7);
            }
            MyDB.DBClose();
            return customerVisitingAbroad;
        }

        public static SqlDataReader GetCustomerWorkExperienceInfo(Guid id)
        {
            string strSQL = "Select * from TB_CustomerWorkExperience where CustomerId=@CustomerId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerWorkExperience GetCustomerWorkExperienceInfoViaId(Guid workExpId)
        {
            SqlDataReader MyReader = null;
            CustomerWorkExperience customerWorkExperience = new CustomerWorkExperience();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerWorkExperience where CustomerWorkExpId = @CustomerWorkExpId";
            MyDB.AddParameters(workExpId, "CustomerWorkExpId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerWorkExperience.CustomerId = MyReader.GetGuid(0);
                customerWorkExperience.StartTime = MyReader.GetDateTime(1);
                customerWorkExperience.EndTime = MyReader.GetDateTime(2);
                customerWorkExperience.Title = ManageOther.DecodeWithBytes(MyReader.GetString(3));
                customerWorkExperience.Company = ManageOther.DecodeWithBytes(MyReader.GetString(4));
                customerWorkExperience.WorkingPlace = ManageOther.DecodeWithBytes(MyReader.GetString(5));
                customerWorkExperience.CustomerWorkExpId = MyReader.GetGuid(6);
            }
            MyDB.DBClose();
            return customerWorkExperience;
        }

        private static void InsertCustomerProduct(Guid customerId, Guid[] productId)
        {
            for (int i = 0; i < productId.Length; i++)
            {
                Guid customerProductId = Guid.NewGuid();
                DBAccess MyDB = new DBAccess();
                MyDB.DBOpen();
                MyDB.AddParameters(customerProductId, "CustomerProductId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(productId[i], "ProductId", SqlDbType.UniqueIdentifier);
                string strSQL = "Insert into TB_CustomerProduct (CustomerProductId,CustomerId,ProductId) values (@CustomerProductId,@CustomerId,@ProductId)";
                MyDB.DBSetSelect(strSQL);
                MyDB.DBExcuteNonQuery();
                MyDB.DBClose();
            }
        }

        public static void InsertNewCCustomerResponsibleStaffInfo(Guid id, string staffName, string staffTitle, DateTime responsibleFromTime, DateTime responsibleToTime)
        {
            DESEncrypt mydes = new DESEncrypt();
            Guid customerResponsibleStaffId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerResponsibleStaffId, "CustomerResponsibleStaffId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(mydes.EncryptString(staffName), "StaffName", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(staffTitle), "StaffTitle", SqlDbType.NVarChar);
            MyDB.AddParameters(responsibleFromTime, "ResponsibleFromTime", SqlDbType.DateTime);
            MyDB.AddParameters(responsibleToTime, "ResponsibleToTime", SqlDbType.DateTime);
            string strSQL = "Insert into TB_CustomerResponsibleStaff (CustomerId,CustomerResponsibleStaffId,StaffName,StaffTitle,ResponsibleFromTime,ResponsibleToTime) values (@CustomerId,@CustomerResponsibleStaffId,@StaffName,@StaffTitle,@ResponsibleFromTime,@ResponsibleToTime)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerASBContractInfo(string contractName, string companyName, string orionNo, string contractValue, DateTime singnedDate, DateTime effectDate, Guid customerId)
        {
            DESEncrypt mydes = new DESEncrypt();
            Guid customerContractId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(contractName), "ContractName", SqlDbType.VarChar);
            MyDB.AddParameters(mydes.EncryptString(companyName), "CompanyName", SqlDbType.NVarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(orionNo), "OrionNo", SqlDbType.VarChar);
            MyDB.AddParameters(mydes.EncryptString(contractValue), "ContractValue", SqlDbType.NVarChar);
            MyDB.AddParameters(singnedDate, "SingnedDate", SqlDbType.DateTime);
            MyDB.AddParameters(effectDate, "EffectDate", SqlDbType.DateTime);
            MyDB.AddParameters(customerContractId, "CustomerContractId", SqlDbType.UniqueIdentifier);
            string strSQL = "Insert into TB_CustomerASBContract (CustomerId,ContractName,CompanyName,OrionNo,ContractValue,SingnedDate,EffectDate,CustomerContractId) values (@CustomerId,@ContractName,@CompanyName,@OrionNo,@ContractValue,@SingnedDate,@EffectDate,@CustomerContractId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerASBEventsInfo(Guid id, DateTime aSBEventTime, string aSBEventName, string aSBEventVenue, string aSBEventTopic, DateTime aSBEventToTime)
        {
            DESEncrypt mydes = new DESEncrypt();
            Guid customerASBEventsId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerASBEventsId, "CustomerASBEventsId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(aSBEventTime, "ASBEventTime", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(aSBEventName), "ASBEventName", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(aSBEventVenue), "ASBEventVenue", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(aSBEventTopic), "ASBEventTopic", SqlDbType.NVarChar);
            MyDB.AddParameters(aSBEventToTime, "ASBEventToTime", SqlDbType.DateTime);
            string strSQL = "Insert into TB_CustomerASBEvents (CustomerId,CustomerASBEventsId,ASBEventTime,ASBEventName,ASBEventVenue,ASBEventTopic,ASBEventToTime) values (@CustomerId,@CustomerASBEventsId,@ASBEventTime,@ASBEventName,@ASBEventVenue,@ASBEventTopic,@ASBEventToTime)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerASBReceptionInfo(Guid id, string receptionHotel, string receptionRestaurant, string receptionTour, DateTime receptionTime, DateTime receptionToTime)
        {
            Guid customerASBReceptionId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerASBReceptionId, "CustomerASBReceptionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(receptionHotel), "ReceptionHotel", SqlDbType.NVarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(receptionRestaurant), "ReceptionRestaurant", SqlDbType.NVarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(receptionTour), "ReceptionTour", SqlDbType.NVarChar);
            MyDB.AddParameters(receptionTime, "ReceptionTime", SqlDbType.DateTime);
            MyDB.AddParameters(receptionToTime, "ReceptionToTime", SqlDbType.DateTime);
            string strSQL = "Insert into TB_CustomerASBReception (CustomerId,CustomerASBReceptionId,ReceptionHotel,ReceptionRestaurant,ReceptionTour,ReceptionTime,ReceptionToTime) values (@CustomerId,@CustomerASBReceptionId,@ReceptionHotel,@ReceptionRestaurant,@ReceptionTour,@ReceptionTime,@ReceptionToTime)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerBasicInfo(Guid id, string name, string gender, string title, int level, Guid companyId, string depart, Guid catId, Guid regionId, Guid areaId, Guid userId, Guid[] productId, string customerKind, string customerEmail, string mailAdd)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(name, "CustomerName", SqlDbType.VarChar);
            MyDB.AddParameters(gender, "CustomerGender", SqlDbType.VarChar);
            MyDB.AddParameters(title, "CustomerTitle", SqlDbType.VarChar);
            MyDB.AddParameters(level, "CustomerLevel", SqlDbType.Int);
            MyDB.AddParameters(companyId, "CompanyId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(depart, "CustomerDepart", SqlDbType.VarChar);
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(new Guid(customerKind), "CustomerDefId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerEmail, "CustomerEmail", SqlDbType.VarChar);
            MyDB.AddParameters(mailAdd, "MailAdd", SqlDbType.VarChar);
            string strSQL = "Insert into TB_CustomerBasicInfo (CustomerId,CustomerName,CustomerGender,CustomerTitle,CustomerLevel,CompanyId,CustomerDepart,CATId,RegionId,AreaId,UserId,CustomerDefId,CustomerEmail,MailAdd) values (@CustomerId,@CustomerName,@CustomerGender,@CustomerTitle,@CustomerLevel,@CompanyId,@CustomerDepart,@CATId,@RegionId,@AreaId,@UserId,@CustomerDefId,@CustomerEmail,@MailAdd)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
            InsertCustomerProduct(id, productId);
        }

        public static void InsertNewCustomerCompetitorEventsInfo(Guid id, DateTime competitorEventTime, string competitorEventName, string competitorEventVenue, string competitorEventTopic, DateTime competitorEventToTime)
        {
            DESEncrypt mydes = new DESEncrypt();
            Guid customerCompetitorEventsId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerCompetitorEventsId, "CustomerCompetitorEventsId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(competitorEventTime, "CompetitorEventTime", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(competitorEventName), "CompetitorEventName", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(competitorEventVenue), "CompetitorEventVenue", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(competitorEventTopic), "CompetitorEventTopic", SqlDbType.NVarChar);
            MyDB.AddParameters(competitorEventToTime, "CompetitorEventToTime", SqlDbType.DateTime);
            string strSQL = "Insert into TB_CustomerCompetitorEvents (CustomerId,CustomerCompetitorEventsId,CompetitorEventTime,CompetitorEventName,CompetitorEventVenue,CompetitorEventTopic,CompetitorEventToTime) values (@CustomerId,@CustomerCompetitorEventsId,@CompetitorEventTime,@CompetitorEventName,@CompetitorEventVenue,@CompetitorEventTopic,@CompetitorEventToTime)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerCompetitorMeetingInfo(Guid id, DateTime cVTime, string cVVenue, string cVExecutives, string cVReasons, string cCompanyName)
        {
            DESEncrypt mydes = new DESEncrypt();
            Guid customerCompetitorMeetingId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerCompetitorMeetingId, "CustomerCompetitorMeetingId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(cVTime, "CVTime", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(cVVenue), "CVVenue", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(cVExecutives), "CVExecutives", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(cVReasons), "CVReasons", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(cCompanyName), "CCompanyName", SqlDbType.NVarChar);
            string strSQL = "Insert into TB_CustomerCompetitorMeeting (CustomerId,CustomerCompetitorMeetingId,CVTime,CVVenue,CVExecutives,CVReasons,CCompanyName) values (@CustomerId,@CustomerCompetitorMeetingId,@CVTime,@CVVenue,@CVExecutives,@CVReasons,@CCompanyName)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerCostumeInfo(Guid id, string neckMeasurement, string shoesSize, string hipMeasurement, string brandPre)
        {
            DESEncrypt mydes = new DESEncrypt();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(mydes.EncryptString(neckMeasurement), "NeckMeasurement", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(shoesSize), "ShoesSize", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(hipMeasurement), "HipMeasurement", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(brandPre), "BrandPre", SqlDbType.NVarChar);
            string strSQL = "Insert into TB_CustomerCostume (CustomerId,NeckMeasurement,ShoesSize,HipMeasurement,BrandPre) values (@CustomerId,@NeckMeasurement,@ShoesSize,@HipMeasurement,@BrandPre)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerEventsInfo(Guid id, DateTime customerEventTime, string customerEventName, string customerEventVenue, string customerEventTopic, string customerEventGift, DateTime customerEventToTime)
        {
            DESEncrypt mydes = new DESEncrypt();
            Guid customerEventsId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerEventsId, "CustomerEventsId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerEventTime, "CustomerEventTime", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(customerEventName), "CustomerEventName", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(customerEventVenue), "CustomerEventVenue", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(customerEventTopic), "CustomerEventTopic", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(customerEventGift), "CustomerEventGift", SqlDbType.NVarChar);
            MyDB.AddParameters(customerEventToTime, "CustomerEventToTime", SqlDbType.DateTime);
            string strSQL = "Insert into TB_CustomerEvents (CustomerId,CustomerEventsId,CustomerEventTime,CustomerEventName,CustomerEventVenue,CustomerEventTopic,CustomerEventGift,CustomerEventToTime) values (@CustomerId,@CustomerEventsId,@CustomerEventTime,@CustomerEventName,@CustomerEventVenue,@CustomerEventTopic,@CustomerEventGift,@CustomerEventToTime)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerEVMeetingInfo(Guid id, DateTime eVTime, string eVVenue, string eVExecutives, string eVReasons)
        {
            DESEncrypt mydes = new DESEncrypt();
            Guid customerEVMeetingId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerEVMeetingId, "CustomerEVMeetingId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(eVTime, "EVTime", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(eVVenue), "EVVenue", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(eVExecutives), "EVExecutives", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(eVReasons), "EVReasons", SqlDbType.NVarChar);
            string strSQL = "Insert into TB_CustomerEVMeeting (CustomerId,CustomerEVMeetingId,EVTime,EVVenue,EVExecutives,EVReasons) values (@CustomerId,@CustomerEVMeetingId,@EVTime,@EVVenue,@EVExecutives,@EVReasons)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerFamilyInfo(Guid id, string memberName, string relationship, DateTime mBirthDate, string mSchoolCompany, string mPhone, string otherInfo)
        {
            DESEncrypt mydes = new DESEncrypt();
            Guid customerFamilyInfoId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerFamilyInfoId, "CustomerFamilyInfoId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(mydes.EncryptString(memberName), "MemberName", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(relationship), "Relationship", SqlDbType.NVarChar);
            MyDB.AddParameters(mBirthDate, "MBirthDate", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(mSchoolCompany), "MSchoolCompany", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(mPhone), "MPhone", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(otherInfo), "OtherInfo", SqlDbType.NVarChar);
            string strSQL = "Insert into TB_CustomerFamilyInfo (CustomerId,CustomerFamilyInfoId,MemberName,Relationship,MBirthDate,MSchoolCompany,MPhone,OtherInfo) values (@CustomerId,@CustomerFamilyInfoId,@MemberName,@Relationship,@MBirthDate,@MSchoolCompany,@MPhone,@OtherInfo)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerHealthInfo(Guid id, int healthStatus, int eyesight, string majorOperation, string specialDisease, string allergy)
        {
            DESEncrypt mydes = new DESEncrypt();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(healthStatus, "HealthStatus", SqlDbType.Int);
            MyDB.AddParameters(eyesight, "Eyesight", SqlDbType.Int);
            MyDB.AddParameters(mydes.EncryptString(majorOperation), "MajorOperation", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(specialDisease), "SpecialDisease", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(allergy), "Allergy", SqlDbType.NVarChar);
            string strSQL = "Insert into TB_CustomerHealth (CustomerId,HealthStatus,Eyesight,MajorOperation,SpecialDisease,Allergy) values (@CustomerId,@HealthStatus,@Eyesight,@MajorOperation,@SpecialDisease,@Allergy)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerImageInfo(Guid customerId, DateTime ImageDate, string imageVenue, string imagePerson, string imageReason, string imageType, byte[] imageObject)
        {
            Guid imageId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBTransactionBegin();
            try
            {
                MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(imageId, "ImageId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(ImageDate, "ImageDate", SqlDbType.DateTime);
                MyDB.AddParameters(ManageOther.EncodeWithBytes(imageVenue), "ImageVenue", SqlDbType.VarChar);
                MyDB.AddParameters(ManageOther.EncodeWithBytes(imagePerson), "ImagePerson", SqlDbType.VarChar);
                MyDB.AddParameters(ManageOther.EncodeWithBytes(imageReason), "ImageReason", SqlDbType.VarChar);
                string strSql = "Insert into Tb_CustomerImageInfo (CustomerId,ImageId,ImageDate,ImageVenue,ImagePerson,ImageReason) values (@CustomerId,@ImageId,@ImageDate,@ImageVenue,@ImagePerson,@ImageReason)";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(imageId, "ImageId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(imageType, "ImageType", SqlDbType.VarChar);
                MyDB.AddParameters(imageObject, "ImageObject", SqlDbType.Image);
                strSql = "Insert into Tb_CustomerImage (ImageId,ImageType,ImageObject) values (@ImageId,@ImageType,@ImageObject)";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.DBTransactionCommit();
            }
            catch (Exception)
            {
                MyDB.DBTransactionRollback();
            }
            MyDB.DBClose();
        }

        public static void InsertNewCustomerImageObjectInfo(Guid imageId, string imageType, byte[] imageObject)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(imageType, "ImageType", SqlDbType.VarChar);
            MyDB.AddParameters(imageObject, "ImageObject", SqlDbType.Image);
            MyDB.AddParameters(imageId, "ImageId", SqlDbType.UniqueIdentifier);
            string strSQL = "Insert into Tb_CustomerImage (ImageType,ImageObject,ImageId) values (@ImageType,@ImageObject,@ImageId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerNewsPaper(Guid customerId, string paperType)
        {
            string strSql = "Insert into TB_CustomerNewsPaper (CustomerId,NewsPaperType,CustomerNewsPaperId) values (@CustomerId,@NewsPaperType,@CustomerNewsPaperId) ";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(paperType, "NewsPaperType", SqlDbType.VarChar);
            MyDB.AddParameters(Guid.NewGuid(), "CustomerNewsPaperId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSql);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerPersonalInfo(Guid id, DateTime birthDate, DateTime marriageDate, string birthPlace, string familyAdd, string relocationAdd, string personalTel, string mobile, string bolldType, string weight, string height, string spcHobbies)
        {
            DESEncrypt mydes = new DESEncrypt();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(birthDate, "CustomerBirthDate", SqlDbType.DateTime);
            MyDB.AddParameters(marriageDate, "CustomerMarriageDate", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(birthPlace), "CustomerBirthPlace", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(familyAdd), "CustomerFamilyAdd", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(relocationAdd), "CustomerRelocationAdd", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(personalTel), "CustomerPersonalTele", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(mobile), "CustomerMobile", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(bolldType), "CustomerBloodType", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(weight), "CustomerWeight", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(height), "CustomerHeight", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(spcHobbies), "SpecialHobbies", SqlDbType.NVarChar);
            string strSQL = "Insert into TB_CustomerPersonalInfo (CustomerId,CustomerBirthDate,CustomerMarriageDate,CustomerBirthPlace,CustomerFamilyAdd,CustomerRelocationAdd,CustomerPersonalTele,CustomerMobile,CustomerBloodType,CustomerWeight,CustomerHeight,SpecialHobbies) values (@CustomerId,@CustomerBirthDate,@CustomerMarriageDate,@CustomerBirthPlace,@CustomerFamilyAdd,@CustomerRelocationAdd,@CustomerPersonalTele,@CustomerMobile,@CustomerBloodType,@CustomerWeight,@CustomerHeight,@SpecialHobbies)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerRelationsInfo(Guid id, string relations)
        {
            DESEncrypt mydes = new DESEncrypt();
            Guid customerRelationsId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerRelationsId, "CustomerRelationsId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(mydes.EncryptString(relations), "Relations", SqlDbType.NVarChar);
            string strSQL = "Insert into TB_CustomerRelations (CustomerId,CustomerRelationsId,Relations) values (@CustomerId,@CustomerRelationsId,@Relations)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerStudyExperienceInfo(Guid id, string degree, string major, string granduateSchool, string sponsor, DateTime startDate, DateTime endDate)
        {
            Guid studyExpId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(studyExpId, "CustomerStudyExpId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(degree), "Degree", SqlDbType.VarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(major), "Major", SqlDbType.VarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(granduateSchool), "GranduateSchool", SqlDbType.VarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(sponsor), "Sponsor", SqlDbType.VarChar);
            MyDB.AddParameters(startDate, "StartDate", SqlDbType.DateTime);
            MyDB.AddParameters(endDate, "EndDate", SqlDbType.DateTime);
            string strSQL = "Insert into TB_CustomerStudyExperience (CustomerId,Degree,Major,Sponsor,StartDate,EndDate,GranduateSchool,CustomerStudyExpId) values (@CustomerId,@Degree,@Major,@Sponsor,@StartDate,@EndDate,@GranduateSchool,@CustomerStudyExpId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerTrainInfo(Guid id, DateTime customerTrainTime, Guid customerTrainTypeId, DateTime customerTrainToTime, Guid[] customerSchoolIdAry)
        {
            Guid customerTrainId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBTransactionBegin();
            try
            {
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(customerTrainTime, "CustomerTrainTime", SqlDbType.DateTime);
                MyDB.AddParameters(customerTrainTypeId, "CustomerTrainTypeId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(customerTrainId, "CustomerTrainId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(customerTrainToTime, "CustomerTrainToTime", SqlDbType.DateTime);
                string strSQL = "Insert into TB_CustomerTraining (CustomerId,CustomerTrainTime,CustomerTrainTypeId,CustomerTrainId,CustomerTrainToTime) values (@CustomerId,@CustomerTrainTime,@CustomerTrainTypeId,@CustomerTrainId,@CustomerTrainToTime)";
                MyDB.DBSetSelect(strSQL);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                string strSQL1 = "";
                for (int i = 0; i < customerSchoolIdAry.Length; i++)
                {
                    MyDB.AddParameters(customerSchoolIdAry[i], "CustomerSchoolId", SqlDbType.UniqueIdentifier);
                    MyDB.AddParameters(customerTrainId, "CustomerTrainId", SqlDbType.UniqueIdentifier);
                    strSQL1 = "Insert into TB_CustomerSchoolTypeMapping (CustomerTrainId,CustomerSchoolId) values (@CustomerTrainId,@CustomerSchoolId)";
                    MyDB.DBSetSelect(strSQL1);
                    MyDB.DBExcuteNonQuery();
                    MyDB.ClearParameters();
                }
                MyDB.DBTransactionCommit();
            }
            catch (Exception)
            {
                MyDB.DBTransactionRollback();
            }
            MyDB.DBClose();
        }

        public static void InsertNewCustomerTravelInfo(Guid id, string coupon, string other)
        {
            Guid customerTravelId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerTravelId, "CustomerTravelId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(coupon), "Coupon", SqlDbType.VarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(other), "Other", SqlDbType.VarChar);
            string strSQL = "Insert into TB_CustomerTravel (CustomerId,CustomerTravelId,Coupon,Other) values (@CustomerId,@CustomerTravelId,@Coupon,@Other)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerVisitingAbroadInfo(Guid id, DateTime aBVTime, string aBVenue, string mainActivity, string aSBAccompany, DateTime aBVToTime)
        {
            DESEncrypt mydes = new DESEncrypt();
            Guid customerVisitingAbroadId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerVisitingAbroadId, "CustomerVisitingAbroadId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(aBVTime, "ABVTime", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(aBVenue), "ABVenue", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(mainActivity), "MainActivity", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(aSBAccompany), "ASBAccompany", SqlDbType.NVarChar);
            MyDB.AddParameters(aBVToTime, "ABVToTime", SqlDbType.DateTime);
            string strSQL = "Insert into TB_CustomerVisitingAbroad (CustomerId,CustomerVisitingAbroadId,ABVTime,ABVenue,MainActivity,ASBAccompany,ABVToTime) values (@CustomerId,@CustomerVisitingAbroadId,@ABVTime,@ABVenue,@MainActivity,@ASBAccompany,@ABVToTime)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCustomerWorkExperienceInfo(Guid id, DateTime startTime, DateTime endTime, string title, string company, string workingPlace)
        {
            Guid workExpId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(workExpId, "CustomerWorkExpId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(title), "Title", SqlDbType.VarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(company), "Company", SqlDbType.VarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(workingPlace), "WorkingPlace", SqlDbType.VarChar);
            MyDB.AddParameters(startTime, "StartTime", SqlDbType.DateTime);
            MyDB.AddParameters(endTime, "EndTime", SqlDbType.DateTime);
            string strSQL = "Insert into TB_CustomerWorkExperience (CustomerId,Title,Company,WorkingPlace,StartTime,EndTime,CustomerWorkExpId) values (@CustomerId,@Title,@Company,@WorkingPlace,@StartTime,@EndTime,@CustomerWorkExpId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static bool IsExistCoustomerViaCompanyId(Guid companyId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(companyId, "CompanyId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerBasicInfo where CompanyId = @CompanyId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistSameCoustomer(string name, string gender)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "CustomerName", SqlDbType.VarChar);
            MyDB.AddParameters(gender, "CustomerGender", SqlDbType.VarChar);
            string strSQL = "select * from TB_CustomerBasicInfo where CustomerName = @CustomerName and CustomerGender=@CustomerGender";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistSameCoustomerCostumeInfo(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerCostume where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistSameCoustomerHealthInfo(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerHealth where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistSameCoustomerPersonalInfo(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerPersonalInfo where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistSameCustomerASBContractInfo(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerASBContract where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistSameCustomerASBEventsInfo(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerASBEvents where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistSameCustomerASBReceptionInfo(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerASBReception where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistSameCustomerEventsInfo(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerEvents where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistSameCustomerEVMeetingInfo(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerEVMeeting where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistSameCustomerResponsibleStaffInfo(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerResponsibleStaff where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistSameCustomerStudyExperienceInfo(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerStudyExperience where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistSameCustomerTrainInfo(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerTraining where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistSameCustomerVisitingAbroadInfo(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerVisitingAbroad where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistSameCustomerWorkExperienceInfo(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerWorkExperience where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static void RemoveCustomerASBContractInfoViaId(Guid customerContractId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerContractId, "CustomerContractId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerASBContract where CustomerContractId = @CustomerContractId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerASBEventsInfoViaId(Guid customerASBEventsId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerASBEventsId, "CustomerASBEventsId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerASBEvents where CustomerASBEventsId = @CustomerASBEventsId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerASBReceptionInfoViaId(Guid customerASBReceptionId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerASBReceptionId, "CustomerASBReceptionId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerASBReception where CustomerASBReceptionId = @CustomerASBReceptionId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerCompetitorEventsInfoViaId(Guid customerCompetitorEventsId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerCompetitorEventsId, "CustomerCompetitorEventsId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerCompetitorEvents where CustomerCompetitorEventsId = @CustomerCompetitorEventsId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerCompetitorMeetingInfoViaId(Guid customerCompetitorMeetingId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerCompetitorMeetingId, "CustomerCompetitorMeetingId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerCompetitorMeeting where CustomerCompetitorMeetingId = @CustomerCompetitorMeetingId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerEventsInfoViaId(Guid customerEventsId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerEventsId, "CustomerEventsId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerEvents where CustomerEventsId = @CustomerEventsId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerEVMeetingInfoViaId(Guid customerEVMeetingId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerEVMeetingId, "CustomerEVMeetingId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerEVMeeting where CustomerEVMeetingId = @CustomerEVMeetingId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerFamilyInfoViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerFamilyInfoId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerFamilyInfo where CustomerFamilyInfoId = @CustomerFamilyInfoId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerImageInfoViaId(Guid imageId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBTransactionBegin();
            try
            {
                MyDB.AddParameters(imageId, "ImageId", SqlDbType.UniqueIdentifier);
                string strSql = "Delete From Tb_CustomerImageInfo where ImageId = @ImageId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(imageId, "ImageId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From Tb_CustomerImage where ImageId = @ImageId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.DBTransactionCommit();
            }
            catch (Exception)
            {
                MyDB.DBTransactionRollback();
            }
            MyDB.DBClose();
        }

        public static void RemoveCustomerImageObjectInfoViaId(Guid imageId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(imageId, "ImageId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From Tb_CustomerImage where ImageId = @ImageId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerNewsPaperViaId(Guid customerNewsPaperId)
        {
            string strSql = "Delete From TB_CustomerNewsPaper where CustomerNewsPaperId = @CustomerNewsPaperId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerNewsPaperId, "CustomerNewsPaperId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSql);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        private static void RemoveCustomerProductViaId(Guid customerId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerProduct where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerRelationsInfoViaId(Guid customerRelationsId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerRelationsId, "CustomerRelationsId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerRelations where CustomerRelationsId = @CustomerRelationsId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerResponsibleStaffInfoViaId(Guid customerResponsibleStaffId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerResponsibleStaffId, "CustomerResponsibleStaffId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerResponsibleStaff where CustomerResponsibleStaffId = @CustomerResponsibleStaffId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerStudyExperienceInfoViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerStudyExpId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerStudyExperience where CustomerStudyExpId = @CustomerStudyExpId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerTrainInfoViaId(Guid customerTrainId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBTransactionBegin();
            try
            {
                MyDB.AddParameters(customerTrainId, "CustomerTrainId", SqlDbType.UniqueIdentifier);
                string strSQL = "Delete From TB_CustomerTraining where CustomerTrainId = @CustomerTrainId";
                MyDB.DBSetSelect(strSQL);
                MyDB.DBExcuteNonQuery();
                string strSQL2 = "Delete From TB_CustomerSchoolTypeMapping where CustomerTrainId = @CustomerTrainId";
                MyDB.DBSetSelect(strSQL2);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.DBTransactionCommit();
            }
            catch (Exception)
            {
                MyDB.DBTransactionRollback();
            }
            MyDB.DBClose();
        }

        public static void RemoveCustomerTravelInfoViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerTravelId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerTravel where CustomerTravelId = @CustomerTravelId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBTransactionBegin();
            try
            {
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                string strSql = "Delete From TB_CustomerBasicInfo where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerPersonalInfo where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerPreference where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerHealth where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerCostume where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerTravel where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerStudyExperience where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerWorkExperience where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerFamilyInfo where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerASBEvents where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerASBReception where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerCompetitorEvents where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerCompetitorMeeting where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerEVMeeting where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerImageInfo where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerRelations where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerResponsibleStaff where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerVisitingAbroad where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerEvents where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
                strSql = "Delete From TB_CustomerNewsPaper where CustomerId = @CustomerId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.DBTransactionCommit();
            }
            catch (Exception)
            {
                MyDB.DBTransactionRollback();
            }
            MyDB.DBClose();
        }

        public static void RemoveCustomerVisitingAbroadInfoViaId(Guid customerVisitingAbroadId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerVisitingAbroadId, "CustomerVisitingAbroadId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerVisitingAbroad where CustomerVisitingAbroadId = @CustomerVisitingAbroadId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerWorkExperienceInfoViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerWorkExpId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerWorkExperience where CustomerWorkExpId = @CustomerWorkExpId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static CustomerPersonalInfo select(string www)
        {
            DESEncrypt mydes = new DESEncrypt();
            SqlDataReader MyReader = null;
            CustomerPersonalInfo customerPersonalInfo = new CustomerPersonalInfo();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerPersonalInfo where CustomerBirthPlace = @CustomerBirthPlace";
            MyDB.AddParameters(mydes.EncryptString(www), "CustomerBirthPlace", SqlDbType.NVarChar);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerPersonalInfo.CustomerId = MyReader.GetGuid(0);
                customerPersonalInfo.BirthDate = MyReader.GetDateTime(1);
                customerPersonalInfo.MarriageDate = MyReader.GetDateTime(2);
                customerPersonalInfo.BirthPlace = mydes.DecryptString(MyReader.GetString(3));
                customerPersonalInfo.FamilyAdd = mydes.DecryptString(MyReader.GetString(4));
                customerPersonalInfo.RelocationAdd = mydes.DecryptString(MyReader.GetString(5));
                customerPersonalInfo.PersonalTele = mydes.DecryptString(MyReader.GetString(6));
                customerPersonalInfo.Mobile = mydes.DecryptString(MyReader.GetString(8));
                customerPersonalInfo.BloodType = mydes.DecryptString(MyReader.GetString(9));
                customerPersonalInfo.Weight = mydes.DecryptString(MyReader.GetString(10));
                customerPersonalInfo.Height = mydes.DecryptString(MyReader.GetString(11));
                customerPersonalInfo.SpecicalHobbies = mydes.DecryptString(MyReader.GetString(12));
            }
            MyDB.DBClose();
            return customerPersonalInfo;
        }

        public static void UpdateCustomerASBContractInfo(string contractName, string companyName, string orionNo, string contractValue, DateTime singnedDate, DateTime effectDate, Guid customerContractId)
        {
            DESEncrypt mydes = new DESEncrypt();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(ManageOther.EncodeWithBytes(contractName), "ContractName", SqlDbType.VarChar);
            MyDB.AddParameters(mydes.EncryptString(companyName), "CompanyName", SqlDbType.NVarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(orionNo), "OrionNo", SqlDbType.VarChar);
            MyDB.AddParameters(mydes.EncryptString(contractValue), "ContractValue", SqlDbType.NVarChar);
            MyDB.AddParameters(singnedDate, "SingnedDate", SqlDbType.DateTime);
            MyDB.AddParameters(effectDate, "EffectDate", SqlDbType.DateTime);
            MyDB.AddParameters(customerContractId, "CustomerContractId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_CustomerASBContract set ContractName=@ContractName,CompanyName=@CompanyName,OrionNo=@OrionNo,ContractValue=@ContractValue,SingnedDate=@SingnedDate,EffectDate=@EffectDate where CustomerContractId=@CustomerContractId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerASBEventsInfo(Guid customerASBEventsId, DateTime aSBEventTime, string aSBEventName, string aSBEventVenue, string aSBEventTopic, DateTime aSBEventToTime)
        {
            DESEncrypt mydes = new DESEncrypt();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerASBEventsId, "CustomerASBEventsId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(aSBEventTime, "ASBEventTime", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(aSBEventName), "ASBEventName", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(aSBEventVenue), "ASBEventVenue", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(aSBEventTopic), "ASBEventTopic", SqlDbType.NVarChar);
            MyDB.AddParameters(aSBEventToTime, "ASBEventToTime", SqlDbType.DateTime);
            string strSQL = "Update TB_CustomerASBEvents set ASBEventTime=@ASBEventTime,ASBEventName=@ASBEventName,ASBEventVenue=@ASBEventVenue,ASBEventTopic=@ASBEventTopic,ASBEventToTime=@ASBEventToTime where CustomerASBEventsId=@CustomerASBEventsId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerASBReceptionInfo(Guid customerASBReceptionId, string receptionHotel, string receptionRestaurant, string receptionTour, DateTime receptionTime, DateTime receptionToTime)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerASBReceptionId, "CustomerASBReceptionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(receptionHotel), "ReceptionHotel", SqlDbType.NVarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(receptionRestaurant), "ReceptionRestaurant", SqlDbType.NVarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(receptionTour), "ReceptionTour", SqlDbType.NVarChar);
            MyDB.AddParameters(receptionTime, "ReceptionTime", SqlDbType.DateTime);
            MyDB.AddParameters(receptionToTime, "ReceptionToTime", SqlDbType.DateTime);
            string strSQL = "Update TB_CustomerASBReception set ReceptionHotel=@ReceptionHotel,ReceptionRestaurant=@ReceptionRestaurant,ReceptionTour=@ReceptionTour,ReceptionTime=@ReceptionTime  where CustomerASBReceptionId=@CustomerASBReceptionId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerBasicInfo(Guid id, string name, string gender, string title, int level, Guid companyId, string depart, Guid catId, Guid regionId, Guid[] productId, string customerKind, string customerEmail, string mailAdd)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(name, "CustomerName", SqlDbType.VarChar);
            MyDB.AddParameters(gender, "CustomerGender", SqlDbType.VarChar);
            MyDB.AddParameters(title, "CustomerTitle", SqlDbType.VarChar);
            MyDB.AddParameters(level, "CustomerLevel", SqlDbType.Int);
            MyDB.AddParameters(companyId, "CompanyId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(depart, "CustomerDepart", SqlDbType.VarChar);
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(new Guid(customerKind), "CustomerDefId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerEmail, "CustomerEmail", SqlDbType.VarChar);
            MyDB.AddParameters(mailAdd, "MailAdd", SqlDbType.VarChar);
            string strSQL = "Update TB_CustomerBasicInfo set CustomerName = @CustomerName,CustomerGender = @CustomerGender,CustomerTitle = @CustomerTitle,CustomerLevel = @CustomerLevel,CompanyId = @CompanyId,CustomerDepart = @CustomerDepart,CATId=@CATId,RegionId=@RegionId,CustomerDefId=@CustomerDefId,CustomerEmail=@CustomerEmail,MailAdd=@MailAdd where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
            UpdateUserProvince(id, productId);
        }

        public static void UpdateCustomerCompetitorEventsInfo(Guid customerCompetitorEventsId, DateTime competitorEventTime, string competitorEventName, string competitorEventVenue, string competitorEventTopic, DateTime competitorEventToTime)
        {
            DESEncrypt mydes = new DESEncrypt();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerCompetitorEventsId, "CustomerCompetitorEventsId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(competitorEventTime, "CompetitorEventTime", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(competitorEventName), "CompetitorEventName", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(competitorEventVenue), "CompetitorEventVenue", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(competitorEventTopic), "CompetitorEventTopic", SqlDbType.NVarChar);
            MyDB.AddParameters(competitorEventToTime, "CompetitorEventToTime", SqlDbType.DateTime);
            string strSQL = "Update TB_CustomerCompetitorEvents set CompetitorEventTime=@CompetitorEventTime,CompetitorEventName=@CompetitorEventName,CompetitorEventVenue=@CompetitorEventVenue,CompetitorEventTopic=@CompetitorEventTopic,CompetitorEventToTime=@CompetitorEventToTime  where CustomerCompetitorEventsId=@CustomerCompetitorEventsId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerCompetitorMeetingInfo(Guid customerCompetitorMeetingId, DateTime cVTime, string cVVenue, string cVExecutives, string cVReasons, string cCompanyName)
        {
            DESEncrypt mydes = new DESEncrypt();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerCompetitorMeetingId, "CustomerCompetitorMeetingId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(cVTime, "CVTime", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(cVVenue), "CVVenue", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(cVExecutives), "CVExecutives", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(cVReasons), "CVReasons", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(cCompanyName), "CCompanyName", SqlDbType.NVarChar);
            string strSQL = "Update TB_CustomerCompetitorMeeting set CVTime=@CVTime,CVVenue=@CVVenue,CVExecutives=@CVExecutives,CVReasons=@CVReasons,CCompanyName=@CCompanyName  where CustomerCompetitorMeetingId=@CustomerCompetitorMeetingId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerCostumeInfo(Guid id, string neckMeasurement, string shoesSize, string hipMeasurement, string brandPre)
        {
            DBAccess MyDB = new DBAccess();
            DESEncrypt mydes = new DESEncrypt();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(mydes.EncryptString(neckMeasurement), "NeckMeasurement", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(shoesSize), "ShoesSize", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(hipMeasurement), "HipMeasurement", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(brandPre), "BrandPre", SqlDbType.NVarChar);
            string strSQL = "Update TB_CustomerCostume set NeckMeasurement=@NeckMeasurement,ShoesSize=@ShoesSize,HipMeasurement=@HipMeasurement,BrandPre=@BrandPre where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerEventsInfo(Guid customerEventsId, DateTime customerEventTime, string customerEventName, string customerEventVenue, string customerEventTopic, string customerEventGift, DateTime customerEventToTime)
        {
            DESEncrypt mydes = new DESEncrypt();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerEventsId, "CustomerEventsId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerEventTime, "CustomerEventTime", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(customerEventName), "CustomerEventName", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(customerEventVenue), "CustomerEventVenue", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(customerEventTopic), "CustomerEventTopic", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(customerEventGift), "CustomerEventGift", SqlDbType.NVarChar);
            MyDB.AddParameters(customerEventToTime, "CustomerEventToTime", SqlDbType.DateTime);
            string strSQL = "Update TB_CustomerEvents set CustomerEventTime=@CustomerEventTime,CustomerEventName=@CustomerEventName,CustomerEventVenue=@CustomerEventVenue,CustomerEventTopic=@CustomerEventTopic,CustomerEventGift=@CustomerEventGift,CustomerEventToTime=@CustomerEventToTime where CustomerEventsId=@CustomerEventsId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerEVMeetingInfo(Guid customerEVMeetingId, DateTime eVTime, string eVVenue, string eVExecutives, string eVReasons)
        {
            DESEncrypt mydes = new DESEncrypt();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerEVMeetingId, "CustomerEVMeetingId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(eVTime, "EVTime", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(eVVenue), "EVVenue", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(eVExecutives), "EVExecutives", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(eVReasons), "EVReasons", SqlDbType.NVarChar);
            string strSQL = "Update TB_CustomerEVMeeting set EVTime=@EVTime,EVVenue=@EVVenue,EVExecutives=@EVExecutives,EVReasons=@EVReasons where CustomerEVMeetingId=@CustomerEVMeetingId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerFamilyInfo(Guid customerFamilyInfoId, string memberName, string relationship, DateTime mBirthDate, string mSchoolCompany, string mPhone, string otherInfo)
        {
            DESEncrypt mydes = new DESEncrypt();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerFamilyInfoId, "CustomerFamilyInfoId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(mydes.EncryptString(memberName), "MemberName", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(relationship), "Relationship", SqlDbType.NVarChar);
            MyDB.AddParameters(mBirthDate, "MBirthDate", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(mSchoolCompany), "MSchoolCompany", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(mPhone), "MPhone", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(otherInfo), "OtherInfo", SqlDbType.NVarChar);
            string strSQL = "Update TB_CustomerFamilyInfo set MemberName=@MemberName,Relationship=@Relationship,MBirthDate=@MBirthDate,MSchoolCompany=@MSchoolCompany,MPhone=@MPhone,OtherInfo=@OtherInfo where CustomerFamilyInfoId = @CustomerFamilyInfoId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerHealthInfo(Guid id, int healthStatus, int eyesight, string majorOperation, string specialDisease, string allergy)
        {
            DBAccess MyDB = new DBAccess();
            DESEncrypt mydes = new DESEncrypt();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(healthStatus, "HealthStatus", SqlDbType.Int);
            MyDB.AddParameters(eyesight, "Eyesight", SqlDbType.Int);
            MyDB.AddParameters(mydes.EncryptString(majorOperation), "MajorOperation", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(specialDisease), "SpecialDisease", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(allergy), "Allergy", SqlDbType.NVarChar);
            string strSQL = "Update TB_CustomerHealth set HealthStatus=@HealthStatus,Eyesight=@Eyesight,MajorOperation=@MajorOperation,SpecialDisease=@SpecialDisease,Allergy=@Allergy where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerImageInfo(Guid customerId, Guid imageId, DateTime ImageDate, string imageVenue, string imagePerson, string imageReason, string imageType, byte[] imageObject)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBTransactionBegin();
            try
            {
                MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(imageId, "ImageId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(ImageDate, "ImageDate", SqlDbType.DateTime);
                MyDB.AddParameters(ManageOther.EncodeWithBytes(imageVenue), "ImageVenue", SqlDbType.VarChar);
                MyDB.AddParameters(ManageOther.EncodeWithBytes(imagePerson), "ImagePerson", SqlDbType.VarChar);
                MyDB.AddParameters(ManageOther.EncodeWithBytes(imageReason), "ImageReason", SqlDbType.VarChar);
                string strSql = "Update  Tb_CustomerImageInfo set CustomerId=@CustomerId,ImageDate=@ImageDate,ImageVenue=@ImageVenue,ImagePerson=@ImagePerson,ImageReason=@ImageReason where ImageId=@ImageId";
                MyDB.DBSetSelect(strSql);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                if (imageObject.Length != 0)
                {
                    MyDB.AddParameters(imageId, "ImageId", SqlDbType.UniqueIdentifier);
                    MyDB.AddParameters(imageType, "ImageType", SqlDbType.VarChar);
                    MyDB.AddParameters(imageObject, "ImageObject", SqlDbType.Image);
                    strSql = "Update  Tb_CustomerImage set ImageId=@ImageId,ImageType=@ImageType,ImageObject=@ImageObject where ImageId=@ImageId";
                    MyDB.DBSetSelect(strSql);
                    MyDB.DBExcuteNonQuery();
                    MyDB.ClearParameters();
                }
                MyDB.DBTransactionCommit();
            }
            catch (Exception)
            {
                MyDB.DBTransactionRollback();
            }
            MyDB.DBClose();
        }

        public static void UpdateCustomerMaster(Guid customerId, Guid userId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_CustomerBasicInfo set UserId = @UserId where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerMasterForGroup(Guid fromId, Guid toId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(fromId, "UserId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(toId, "MasterId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_CustomerBasicInfo set UserId = @MasterId where UserId = @UserId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerMasterViaCustomerId(Guid customerId, Guid userId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_CustomerBasicInfo set UserId = @UserId where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerPersonalInfo(Guid id, DateTime birthDate, DateTime marriageDate, string birthPlace, string familyAdd, string relocationAdd, string personalTel, string mobile, string bolldType, string weight, string height, string spcHobbies)
        {
            DBAccess MyDB = new DBAccess();
            DESEncrypt mydes = new DESEncrypt();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(birthDate, "CustomerBirthDate", SqlDbType.DateTime);
            MyDB.AddParameters(marriageDate, "CustomerMarriageDate", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(birthPlace), "CustomerBirthPlace", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(familyAdd), "CustomerFamilyAdd", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(relocationAdd), "CustomerRelocationAdd", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(personalTel), "CustomerPersonalTele", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(mobile), "CustomerMobile", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(bolldType), "CustomerBloodType", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(weight), "CustomerWeight", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(height), "CustomerHeight", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(spcHobbies), "SpecialHobbies", SqlDbType.NVarChar);
            string strSQL = "Update TB_CustomerPersonalInfo set CustomerBirthDate=@CustomerBirthDate,CustomerMarriageDate=@CustomerMarriageDate,CustomerBirthPlace=@CustomerBirthPlace,CustomerFamilyAdd=@CustomerFamilyAdd,CustomerRelocationAdd=@CustomerRelocationAdd,CustomerPersonalTele=@CustomerPersonalTele,CustomerMobile=@CustomerMobile,CustomerBloodType=@CustomerBloodType,CustomerWeight=@CustomerWeight,CustomerHeight=@CustomerHeight,SpecialHobbies=@SpecialHobbies where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerRelationsInfo(Guid customerRelationsId, string relations)
        {
            DESEncrypt mydes = new DESEncrypt();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerRelationsId, "CustomerRelationsId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(mydes.EncryptString(relations), "Relations", SqlDbType.NVarChar);
            string strSQL = "Update TB_CustomerRelations set Relations=@Relations  where CustomerRelationsId=@CustomerRelationsId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerResponsibleStaffInfo(Guid customerResponsibleStaffId, string staffName, string staffTitle, DateTime responsibleFromTime, DateTime responsibleToTime)
        {
            DESEncrypt mydes = new DESEncrypt();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerResponsibleStaffId, "CustomerResponsibleStaffId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(mydes.EncryptString(staffName), "StaffName", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(staffTitle), "StaffTitle", SqlDbType.NVarChar);
            MyDB.AddParameters(responsibleFromTime, "ResponsibleFromTime", SqlDbType.DateTime);
            MyDB.AddParameters(responsibleToTime, "ResponsibleToTime", SqlDbType.DateTime);
            string strSQL = "Update TB_CustomerResponsibleStaff set StaffName=@StaffName,StaffTitle=@StaffTitle,ResponsibleFromTime=@ResponsibleFromTime,ResponsibleToTime=@ResponsibleToTime  where CustomerResponsibleStaffId=@CustomerResponsibleStaffId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerStudyExperienceInfo(Guid studyExpId, string degree, string major, string granduateSchool, string sponsor, DateTime startDate, DateTime endDate)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(studyExpId, "CustomerStudyExpId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(degree), "Degree", SqlDbType.VarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(major), "Major", SqlDbType.VarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(sponsor), "Sponsor", SqlDbType.VarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(granduateSchool), "GranduateSchool", SqlDbType.VarChar);
            MyDB.AddParameters(startDate, "StartDate", SqlDbType.DateTime);
            MyDB.AddParameters(endDate, "EndDate", SqlDbType.DateTime);
            string strSQL = "Update TB_CustomerStudyExperience set Degree=@Degree,Major=@Major,Sponsor=@Sponsor,StartDate=@StartDate,EndDate=@EndDate,GranduateSchool=@GranduateSchool where CustomerStudyExpId = @CustomerStudyExpId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerTrainInfo(Guid customerTrainId, DateTime customerTrainTime, Guid customerTrainTypeId, DateTime customerTrainToTime, Guid[] customerSchoolIdAry)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBTransactionBegin();
            try
            {
                MyDB.AddParameters(customerTrainId, "CustomerTrainId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(customerTrainTime, "CustomerTrainTime", SqlDbType.DateTime);
                MyDB.AddParameters(customerTrainTypeId, "CustomerTrainTypeId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(customerTrainToTime, "CustomerTrainToTime", SqlDbType.DateTime);
                string strSQL = "Update TB_CustomerTraining set CustomerTrainTime=@CustomerTrainTime,CustomerTrainTypeId=@CustomerTrainTypeId,CustomerTrainToTime=@CustomerTrainToTime where CustomerTrainId=@CustomerTrainId";
                MyDB.DBSetSelect(strSQL);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                MyDB.AddParameters(customerTrainId, "CustomerTrainId", SqlDbType.UniqueIdentifier);
                string strSQL2 = "Delete From TB_CustomerSchoolTypeMapping where customerTrainId = @customerTrainId";
                MyDB.DBSetSelect(strSQL2);
                MyDB.DBExcuteNonQuery();
                MyDB.ClearParameters();
                string strSQL1 = "";
                for (int i = 0; i < customerSchoolIdAry.Length; i++)
                {
                    MyDB.AddParameters(customerSchoolIdAry[i], "CustomerSchoolId", SqlDbType.UniqueIdentifier);
                    MyDB.AddParameters(customerTrainId, "CustomerTrainId", SqlDbType.UniqueIdentifier);
                    strSQL1 = "Insert into TB_CustomerSchoolTypeMapping (CustomerTrainId,CustomerSchoolId) values (@CustomerTrainId,@CustomerSchoolId)";
                    MyDB.DBSetSelect(strSQL1);
                    MyDB.DBExcuteNonQuery();
                    MyDB.ClearParameters();
                }
                MyDB.DBTransactionCommit();
            }
            catch (Exception)
            {
                MyDB.DBTransactionRollback();
            }
            MyDB.DBClose();
        }

        public static void UpdateCustomerTravelInfo(Guid customerTravelId, string coupon, string other)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerTravelId, "CustomerTravelId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(coupon), "Coupon", SqlDbType.VarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(other), "Other", SqlDbType.VarChar);
            string strSQL = "Update TB_CustomerTravel set Coupon=@Coupon,Other=@Other where CustomerTravelId = @CustomerTravelId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerTravelInfoOfTranToole(Guid customerId, string transportToolUsed)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(transportToolUsed, "TransportToolUsed", SqlDbType.VarChar);
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_CustomerBasicInfo set TransportToolUsed=@TransportToolUsed where CustomerId = @CustomerId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerVisitingAbroadInfo(Guid customerVisitingAbroadId, DateTime aBVTime, string aBVenue, string mainActivity, string aSBAccompany, DateTime aBVToTime)
        {
            DESEncrypt mydes = new DESEncrypt();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerVisitingAbroadId, "CustomerVisitingAbroadId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(aBVTime, "ABVTime", SqlDbType.DateTime);
            MyDB.AddParameters(mydes.EncryptString(aBVenue), "ABVenue", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(mainActivity), "MainActivity", SqlDbType.NVarChar);
            MyDB.AddParameters(mydes.EncryptString(aSBAccompany), "ASBAccompany", SqlDbType.NVarChar);
            MyDB.AddParameters(aBVToTime, "ABVToTime", SqlDbType.DateTime);
            string strSQL = "Update TB_CustomerVisitingAbroad set ABVTime=@ABVTime,ABVenue=@ABVenue,MainActivity=@MainActivity,ASBAccompany=@ASBAccompany,ABVToTime=@ABVToTime where CustomerVisitingAbroadId=@CustomerVisitingAbroadId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCustomerWorkExperienceInfo(Guid workExpId, DateTime startTime, DateTime endTime, string title, string company, string workingPlace)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(workExpId, "CustomerWorkExpId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(title), "Title", SqlDbType.VarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(company), "Company", SqlDbType.VarChar);
            MyDB.AddParameters(ManageOther.EncodeWithBytes(workingPlace), "WorkingPlace", SqlDbType.VarChar);
            MyDB.AddParameters(startTime, "StartTime", SqlDbType.DateTime);
            MyDB.AddParameters(endTime, "EndTime", SqlDbType.DateTime);
            string strSQL = "Update TB_CustomerWorkExperience set Title=@Title,Company=@Company,WorkingPlace=@WorkingPlace,StartTime=@StartTime,EndTime=@EndTime where CustomerWorkExpId = @CustomerWorkExpId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateUserProvince(Guid customerId, Guid[] productId)
        {
            RemoveCustomerProductViaId(customerId);
            InsertCustomerProduct(customerId, productId);
        }
    }
}

